Solved

ADO Command Run function on Oracle Server.

Posted on 2001-06-21
6
477 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:RRR
  • 4
  • 2
6 Comments
 
LVL 5

Expert Comment

by:rkot2000
ID: 6214422
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
 
LVL 3

Author Comment

by:RRR
ID: 6222794
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
 
LVL 3

Author Comment

by:RRR
ID: 6222814
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Author Comment

by:RRR
ID: 6227788
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
 
LVL 5

Accepted Solution

by:
rkot2000 earned 200 total points
ID: 6227825
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
 
LVL 3

Author Comment

by:RRR
ID: 6230086
Is your connection.provider = "MSDAORA.1" OR connection.provider = "OraOLEDB.Oracle" ?

RRR.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now