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.
dhaneshgesotaAsked:
Who is Participating?
 
leonstrykerCommented:
0
 
catchmeifuwantCommented:
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
0
 
dhaneshgesotaAuthor Commented:
No solution found. Kindly abandon this question.

Thanks!
0
 
leonstrykerCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.