ADO Command Run function on Oracle Server.

Hi, Experts.
I need to execute the function that located on Oracle server, that returns a REF Cursor type.
I have a connection opened in ADO.Connection object.

How can I run the function and what declarations of variables for REF CURSOR should I declare (maybe Recordset or variant)? Give some example, please.
The function in oracle looks like this:

Function Foo(par_exr IN NUMBER)
    RETURN MyDEFINITIONS.ref_cur_LRs
    IS c_LRs MyDEFINITIONS.ref_cur_LRs;
BEGIN
    OPEN c_LRs FOR
      SELECT *
      FROM "SYSTEM"."LRs" s
        WHERE s.EXR = par_exr
        ORDER BY s.EXNAME;
       
      RETURN (c_LRs);  
END Foo;

The package declaration is :
Package MyDEFINITIONS
    IS
    TYPE ref_cur_LRs IS REF CURSOR;
     
    FUNCTION Foo(par_exr IN NUMBER) RETURN
    ref_cur_LRs;
     
END MyDEFINITIONS;

Thanks, RRR.
LVL 3
RRRAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rkot2000Connect With a Mentor Commented:
You need MS OLEDB Provider for Oracle, ADO 2.5, Oracle 7.3+

you can find some info on support.microsoft.com
my search about : select Visual Basic or ADO

my question is : oracle
0
 
rkot2000Commented:
Note:
REF CURSOR argument should be last argument in Oracle procedure
______________________________________
Oracle Side

Package Person
--Spec
CREATE OR REPLACE PACKAGE BODY TESTI.PACKPERSON
  -- or this way as "ref cursor" (pointer to the recordset)
    -- Note: Argument "ref cursor" in procedure should be IN or IN OUT
    CURSOR cperson
            IS SELECT * FROM person;
    TYPE Person_cur IS REF CURSOR RETURN cperson%ROWTYPE;
IS
PROCEDURE allperson_ref(v_SSN IN person.ssn%type,
                            rs IN OUT Person_cur);
END packperson;


--Body
CREATE OR REPLACE PACKAGE BODY TESTI.PACKPERSON
IS
PROCEDURE allperson_ref(v_SSN     IN person.ssn%type,
                           rs IN OUT Person_cur )

IS
 BEGIN
    OPEN rs FOR SELECT * FROM person WHERE ssn=v_SSN;
   
 END;
END;

-------------------------------------------------------------------------
-VB side
-------------------------------------------------------------------------
 Set cmdGetAll = New ADODB.Command
  With cmdGetAll
      Set .ActiveConnection = oConn
      .CommandText = "PACKPERSON.allperson_ref "
      .CommandType = adCmdStoredProc
      vParamArr = Array(555662222)
      Set rs = .Execute(, vParamArr)
END with
0
 
RRRAuthor Commented:
Hi, rkot2000.
I tried your code it is felpful, but I steel can't return a recordset from Oracle procedure.
Here is my code:

1) I created new package
MYPACKAGE

  IS
CURSOR cLRRR
  IS SELECT * FROM "SYSTEM"."LRs";

TYPE curLRRR IS REF CURSOR RETURN cLRRR%ROWTYPE;
PROCEDURE ALL_LRRR(v_Examm IN "SYSTEM"."LRs"."EXAMSS"%TYPE,
          rs IN OUT curLRRR);
END MYPACKAGE;

2) Created new procedure
ALL_LRRR
 (v_Examm IN "SYSTEM"."LRs"."EXAMSS"%TYPE, rs IN OUT curLRRR)IS
BEGIN
    OPEN rs FOR
    SELECT *  
      FROM "SYSTEM"."LRs"
    WHERE "SYSTEM"."LRs"."EXAMSS"=v_Examm;
END ALL_LRRR;

3) Here the VB code:
....
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
With cmd
 .ActiveConnection = cn
REM cn is active connection opened to Oracle server
 .CommandType = adCmdStoredProc
 .CommandText = Trim$(Me.txtSPName.Text)
 
 .Parameters.Append .CreateParameter( _
  "v_examm", adNumeric, adParamInput, , Me.txtParam1.Text)
 
 .Parameters.Append .CreateParameter( _
   "rs", adVariant, adParamOutput, , rs)
 .Execute
End With

MsgBox cmd.Parameters("rs").Value
............

I have this error message on execute method:
ORA-01036: Illegal variable name/number -2147467259


What I is wrong? I think that adVariant datatype for rs parameter is not so good. If I am right tell me that should I use to receive the REF CURSOR datatype?
Or suggest me that to do.
Thanks, RRR.
P.S. I am increase the points for you.
Thanks again.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
RRRAuthor Commented:
Sorry , Some correction:

The error message was on creating second parameter (rs) and the message was:
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. 3001.

RRR.
0
 
RRRAuthor Commented:
Hi, rkot2000.
Can you paste the code for connection string ( I need to see the provider with it you are establishing the connection).
I think that we are working with different providers.

Thanks, RRR.
0
 
RRRAuthor Commented:
Is your connection.provider = "MSDAORA.1" OR connection.provider = "OraOLEDB.Oracle" ?

RRR.
0
All Courses

From novice to tech pro — start learning today.