hi all,
 
I am trying to call oracle stored procedure and it returns reference cursor and other values using macro (not only refrence cursor).
the problem is I can't define variable as OracleDbType
 
could you please help :)
 
 
the code:
 
Option Explicit
Dim Cn As ADODB.Connection
 
Sub TestStoredProcedure()
 
Dim CServer As String
Dim CDatabase As String
Dim CLogon As String
Dim CPass As String
Dim StartDate As Date
Dim EndDate As Date
Dim TEndDate As String
Dim inp_acct As String
Dim return_status As Integer
Dim db_app_err As Integer
Dim err_text_nat_lan As String
Dim err_text_eng As String
Dim inp_start_bal As Integer
Dim out_tra As OracleDbType
 
 
 
 
 
 
 
StartDate = "1/1/2010"
EndDate = "10/10/2010"
out_tra
 
 
 
 
 
 
       CServer = "BANKS" ' Your server name here
       CDatabase = "TEST" ' Your database name here
       CLogon = "oper" ' your logon here
       CPass = "oper" ' your password here
       Call CnToSQL(CServer, CDatabase, CLogon, CPass)
       Call EXE("PA07EDCU0.A07EDCU3", "020210000000012100001", StartDate, EndDate, 0, inp_acct, inp_start_bal, out_tra, return_status, db_app_err, err_text_nat_lan, err_text_eng)
               
    
 
End Sub
Public Sub EXE(StoredProc As String, Para1 As String, Para2 As Date, Para3 As Date, Para4 As Integer, Para5 As String, Para6 As Integer, out_tra As OraRef, Para8 As Integer, Para9 As Integer, Para10 As String, Para11 As String)
'                                     #### Runs Stored Procedure  ####
On Error GoTo ErrHandler
 
Dim Cmd1 As New ADODB.Command
Dim rs As New ADODB.Recordset
 Cmd1.Parameters.Append Cmd1.CreateParameter(Type:=adVariant, Direction:=adParamInput, Size:=1000, Value:="020210000000012100001")
 Cmd1.Parameters.Append Cmd1.CreateParameter(Type:=adDate, Direction:=adParamInput, Value:=CDate(Para2))
 Cmd1.Parameters.Append Cmd1.CreateParameter(Type:=adDate, Direction:=adParamInput, Value:=CDate(Para3))
 Cmd1.Parameters.Append Cmd1.CreateParameter(Type:=adNumeric, Direction:=adParamInput, Value:=0)
 Cmd1.Parameters.Append Cmd1.CreateParameter(Type:=adVariant, Direction:=adParamOutput, Size:=1000)
 Cmd1.Parameters.Append Cmd1.CreateParameter(Type:=adVariant, Direction:=adParamOutput, Size:=1000)
 Cmd1.Parameters.Append Cmd1.CreateParameter(Type:=adNumeric, Direction:=adParamOutput)
 Cmd1.Parameters.Append Cmd1.CreateParameter(Type:=RefCursor, Direction:=adParamOutput)
 Cmd1.Parameters.Append Cmd1.CreateParameter(Type:=adNumeric, Direction:=adParamInputOutput)
 Cmd1.Parameters.Append Cmd1.CreateParameter(Type:=adNumeric, Direction:=adParamInputOutput)
 Cmd1.Parameters.Append Cmd1.CreateParameter(Type:=adVariant, Direction:=adParamInputOutput, Size:=1000)
 Cmd1.Parameters.Append Cmd1.CreateParameter(Type:=adVariant, Direction:=adParamInputOutput, Size:=1000)
 
 Cmd1.CommandType = adCmdText
 Cmd1.ActiveConnection = Cn
 Cmd1.CommandText = "{CALL" & StoredProc & "(?,?,?,?,?,?,?,?,?,?,?)}"
 
 Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenDynamic
    rs = Cmd1.Execute()
 
 ' rs.Open Cmd1 ' Cn,adOpenStatic,adLockReadOnly,-1)
 
' Sheet1.Activate
' Sheet1.Range("A2").CopyFromRecordset rs
' Cn.Close
Exit Sub
 
ErrHandler:
MsgBox ("Problem Running Stored Procedure " & StoredProc)
Resume Next
End Sub
 
 
 
 
Public Sub CnToSQL(ServerName As String, DatabaseName As String, UserId As String, Pass As String)
'                                     #### Connect to DB Sever ####
On Error GoTo ErrHandler
 
Set Cn = New ADODB.Connection
 
 
 
' strConnection = "Driver={Microsoft ODBC for Oracle};" & _
' "Server=BANKS;uid=operation;pwd=oper123;"
'Driver={Microsoft ODBC for Oracle}
 
' Cn.ConnectionString = "Provider=msdaora.1;Server=TEST;uid=operation;pwd=123;PLSQLRSet=1;"
 
Cn.ConnectionString = "Provider=msdaora.1;data source=DERAYAH_PROD;user id=operation;password=oper123;PLSQLRSet=1;"
Cn.Open
If Cn.State <> 1 Then
MsgBox ("Problem with connection " & ServerName & " " & DatabaseName)
End If
Exit Sub
 
ErrHandler:
MsgBox ("Problem with connection " & ServerName & " " & DatabaseName)
Resume Next
End Sub

thanks, Gerrit.
 
 
But, IT IS NOT CLEAR.
 
AND all the examples I found in the internet are to call a proc wich returns one parameter as ref cursor.
 
My procedure returns more than one parameter, one of them is ref cursor.
 
is it posiple to do it by  resultset_from_proc  command?
 
if yes, example will be helpfull
 
I need your help please.
 
thanks.

thanks for your help.
 
could you please tell me how to do the creation of a wrapper stored procedure or function to only send the ref cursor back?
 
thanks again.

you mean in the oracle it self I need to create the new procedure? then call it from the VBA?
 
 
in this way, I can't. I do not have the access to the Oracle DB.
 

accualy, I need also the other output paramenters, in this case I can't use the VBA to do my needs.
 
thanks for your help.