Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
call oracle stored procedure with reference cursor (Read 14640 times)
Thamer
YaBB Newbies
*


I Love SQL*XL

Posts: 5
call oracle stored procedure with reference cursor
30.10.10 at 14:47:47
 
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
Back to top
 
« Last Edit: 31.10.10 at 07:48:36 by Thamer »  
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: call oracle stored procedure with reference cu
Reply #1 - 03.11.10 at 08:58:40
 
Hi,
 
To return a ref cursor from a stored procedure you can use the resultset_from_proc command. This is a SQL*XL command that receives the ref cursor as output from the stored procedure. SQL*XL will handle this data set in the same way it handles the results from a select statement.
 
Please read the following page. There is a worked example on the page:
http://www.oraxcel.com/projects/sqlxl/help/commands/resultset_from_proc.html
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Thamer
YaBB Newbies
*


I Love SQL*XL

Posts: 5
Re: call oracle stored procedure with reference cu
Reply #2 - 06.11.10 at 08:53:12
 
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 Smiley
 
I need your help please.
 
thanks.
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: call oracle stored procedure with reference cu
Reply #3 - 10.11.10 at 11:27:44
 
I don't think that mixing in bind variables will work. If all other variables are input variables you can decide to hard code them or use substitution variables for them.
 
Another elegant solution would be the creation of a wrapper stored procedure or function to only send the ref cursor back.
 
Hope this helps.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Thamer
YaBB Newbies
*


I Love SQL*XL

Posts: 5
Re: call oracle stored procedure with reference cu
Reply #4 - 20.11.10 at 08:15:08
 
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.
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: call oracle stored procedure with reference cu
Reply #5 - 20.11.10 at 14:29:44
 
Hi,
 
In general this would go as follows. I have created the example below just with number parameters (and have not checked the syntax):
 
Suppose the procedure you wish to call has the syntax:
 
many_param_proc( a in number, b out number, c out number, d out number);
 
You can create a procedure to only return parameter b as follows:
 
create or replace procedure myproc( a in number, b out number)
as
  c number;
  d number;
begin
  many_param_proc( a, b, c, d) ;
end;
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Thamer
YaBB Newbies
*


I Love SQL*XL

Posts: 5
Re: call oracle stored procedure with reference cu
Reply #6 - 20.11.10 at 14:41:32
 
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.
 
Back to top
 
« Last Edit: 20.11.10 at 14:42:04 by Thamer »  
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: call oracle stored procedure with reference cu
Reply #7 - 20.11.10 at 15:54:50
 
I think that there is a problem then. The only way SQL*XL can call a procedure and receive a ref cursor back is when there are no other output variables but the ref cursor. Input variables can be passed. Either hardcoded - naturally - or as substitution variables.  
 
Please read the following topic about passing input variables to a proc receiving back a refcursor.
 
Passing variables to proc returning a ref cursor  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1201723955
 
However when your procedure returns more than just the refcursor there is a problem if you cannot create a small wrapper stored procedure.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Thamer
YaBB Newbies
*


I Love SQL*XL

Posts: 5
Re: call oracle stored procedure with reference cu
Reply #8 - 21.11.10 at 08:39:19
 
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.
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: call oracle stored procedure with reference cu
Reply #9 - 21.11.10 at 12:43:57
 
One possibility that is still open is the execution of an anonymous PL/SQL block and the use of bind variables.
 
In SQL*XL you can also run an anonymous PL/SQL block. You could call your complex procedure and with some PL/SQL programming put your output in bind variables.  
 
As an example consider the following. This will run a procedure and put the output variables of the procedure call into bind variables that have the same name as Excel cells so SQL*XL puts them directly into the cells. Thinking about it, this is a very nice way to solve your problem. The only thing you need to do additionally is to loop the refcursor to retrieve the details and to put it into a variable to return to Excel.
 
DECLARE
  n number
  v varchar2(100);
BEGIN
  my_complex_procedure(n,v);
   
  :A1 := n;
  :A2 := v;
END;
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1