?
Solved

ADO Command Run function on Oracle Server.

Posted on 2001-06-21
6
Medium Priority
?
495 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 800 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

800 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