Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ADO Command Run function on Oracle Server.

Posted on 2001-06-21
6
Medium Priority
?
499 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Suggested Courses

596 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