Thamer
YaBB Newbies

I Love SQL*XL
Posts: 5
|
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
|