Solved

ADO Command Run function on Oracle Server.

Posted on 2001-06-21
6
472 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
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…

705 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

20 Experts available now in Live!

Get 1:1 Help Now