asked on

How to call an Oracle SP with OUT cursor and number parameter from VB?

I am using VB 6 as front end and Oracle 9i as backend

To retrieve a set of records to the vb front end i am calling a stored procedure with the following signature:

--this is the package
TYPE GetXmlcursor IS REF CURSOR;
PROCEDURE EXT_WARR_CUST_SEL(v_Cust_AcctNum IN NUMBER, PGetXmlcursor OUT GetXmlcursor, v_nRetVal OUT NUMBER );

--this is the package body
PROCEDURE CUST_SEL(v_Cust_AcctNum IN NUMBER, PGetXmlcursor OUT GetXmlcursor, v_nRetVal OUT NUMBER) IS

      --this code is just to illustrate, actual code is alot of stuff
      OPEN PGetXmlcursor FOR
            select * from cust_info;

      v_nRetVal:=5;
END;

Now the above SP will query a table, populate its data in a out cursor and populate a out parameter with a value.

From Visual Basic I execute it in the following fashion:

Set objCustDetails = New ADODB.Recordset
Set objCmdGetData = New ADODB.Command
Set objCmdGetData.ActiveConnection = mobjDBConnection

objCmdGetData.CommandType = adCmdStoredProc
objCmdGetData.CommandText = Oracle_SP.strRetrieveCustDet 'contains the name of the SP - CUST_SEL

objCmdGetData.Parameters.Append objCmdGetData.CreateParameter("@strAccountNbr", adVarChar, adParamInput, 10)
objCmdGetData.Parameters.Append objCmdGetData.CreateParameter("@strReturn", adInteger, adParamOutput, 10)
objCmdGetData("@strAccountNbr") = strAccountNbr

Set objCustDetails = objCmdGetData.Execute

'getting the out parameter
intDBReturn = objCmdGetData("@strReturn")

The Cursor gets populated properly with the values from the table. The same is properly reflected in recordset. However, the out number parameter returns EMPTY. (@strReturn is empty, even though we are populating the value '5' to it from the SP.)

My question is when a SP is returning a cursor can we or how do we return another out parameter (string or number)?
Because if I remove the out cursor from the SP's signature then it works fine.

TIA,
Dhanesh.

ASKER CERTIFIED SOLUTION

leonstryker

membership

This solution is only available to members.

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

Start Free Trial

catchmeifuwant

well, sp should be able to return the both the variables....I don't know in VB, but in Oracle this is perfectly normal..

Here's an example:

===============================================

SQL> CREATE OR REPLACE package pkg_ref_cursor
2 as
3
4 type ref_cur_type is ref cursor;
5
6
7 end;
8 /

Package created.

SQL> CREATE OR REPLACE procedure sp_ref_cursor(emp_cur out pkg_ref_cursor.ref_cur_type, retval out n
umber)
2 as
3
4 begin
5
6
7 open emp_cur
8 for
9 select * from emp;
10
11 retval := 5;
12 end;
13 /

Procedure created.

SQL> variable c refcursor
SQL> variable d number
SQL>
SQL>
SQL> exec sp_ref_cursor(:c,:d);

PL/SQL procedure successfully completed.

SQL>
SQL> print :d

D
----------
5

SQL> print :c

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 09-DEC-82 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 12-JAN-83 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

1234 MILLER CLERK 7782 23-JAN-82 1300
78

15 rows selected.

======================================================================

HTH

dhaneshgesota

ASKER

No solution found. Kindly abandon this question.

Thanks!

leonstryker

That is because ADO does not work this way. The result must be returned with a SELECT statement instead.

The link supplied has the complete answer.