asked on

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.

rkot2000

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

RRR

ASKER

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.

RRR

ASKER

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.

RRR

ASKER

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.

ASKER CERTIFIED SOLUTION

rkot2000

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RRR

ASKER

Is your connection.provider = "MSDAORA.1" OR connection.provider = "OraOLEDB.Oracle" ?

RRR.