PDA

View Full Version : Run-time error when using OraOLEDB.Oracle Provider



lionne
03-23-2014, 10:25 AM
Hello everyone,
I have faced the following problem. I had a perfectly working code to call oracle stored procedures using MSDAORA. However, connection to Oracle DB doesn't work from Win 64-bit. AFAIK Microsoft doesn't support MSDAORA driver for 64bit Windows, so that I will need to use another driver. I have set up the connection using Oracle Provider for OLEDB. However, I have difficulties calling stored procedures, and unfortunately cannot find any code..

Here's the initial code, that worked perfectly:


Sub connection()
Dim cn As ADODB.Connection
Dim cmd_prod As New ADODB.Command
Set cn = New ADODB.Connection

With cn
.ConnectionString = "Provider=MSDAORA.1;Data Source=datasrc;User Id=user_id;Password=pwd" 'I omit declaration and initialization of these variables
.CursorLocation = adUseClient
.Open
End With

With cmd_prod
Set .ActiveConnection = cn
.CommandText = "s#mdb$stg_da_extr_util.get_all_secevt_ot_aft_mpa_ids"
.CommandType = adCmdStoredProc

Set ParamReturn = .CreateParameter("rslt_ot_aft", adLongVarChar, adParamReturnValue, 10000000)
.Parameters.Append ParamReturn
Set i_caller = .CreateParameter("Caller Identification", adVarChar, adParamInput, 50, "STG_DATA_REQUEST")
.Parameters.Append i_caller

.Execute , , adExecuteNoRecords
ot_aft = .Parameters("rslt_ot_aft")

varSplit = Split(ot_aft, ";")
'first row
row = 2
'first column
col_cnt = 1
For i = 0 To UBound(varSplit)
idx = varSplit(i)

'here the data is splitted and excel tables are populated
...

Next i
End With



Now I have changed the connection string to the following:



With cn
.ConnectionString = "Provider=ORAOLEDB.Oracle;Data Source=" & ih.Cells(1, 2) & ";User Id=" & ih.Cells(2, 2) & ";Password=" & ih.Cells(3, 2) & " "
.CursorLocation = adUseClient
.Open 'the code breaks here stating: Run-Time error '3706' Provider cannot be found. It may not be properly installed.
End With



Can anyone please give me a hint what am I doing wrong?

The Oracle client 11.0.2 is installed.