fetching results from oracle SP [cursor]

Question

I am trying to fetch results from an oracle SP which is returning cursors. I have the following code which does not seem to work...

declare VARIABLE csr1 REFCURSOR;
declare VARIABLE csr2 REFCURSOR;
declare VARIABLE csr3 REFCURSOR;
declare VARIABLE csr4 REFCURSOR;
declare VARIABLE csr5 REFCURSOR;
EXEC getReportData('PUB',:csr1,:csr2,:csr3,:csr4,:csr5); 
PRINT csr1;

I looked around SO for this type of question and that is there I got the above code.

In that same answer there is a second option to loop as well. but that will not work for me since one of the lines in that code says:

rej_rec Reasons_for_Rejection%ROWTYPE;

Reasons_for_Rejection is supposedly the table name...however in my SP they are using some internal table because query is like this:

select c.blah from (select blah1 from blah) c

Answer 1

You should say something more specific than "does not seem to work". But the first thing I see wrong with your sample is that you are confusing SQLPlus and PL/SQL.

I'm assuming your typing the commands, or running them as a script, in SQLPlus.

"DECLARE" begins a PL/SQL block, which won't be executed until a forward slash is entered on a new line. So if you are typing these commands in interactively, it is probably just prompting you for more input:

SQL> declare VARIABLE csr1 REFCURSOR;
  2  declare VARIABLE csr2 REFCURSOR;
  3  declare VARIABLE csr3 REFCURSOR;
  4  declare VARIABLE csr4 REFCURSOR;
  5  declare VARIABLE csr5 REFCURSOR;
  6  EXEC getReportData('PUB',:csr1,:csr2,:csr3,:csr4,:csr5); 
  7  PRINT csr1;
  8  /
SP2-0552: Bind variable "CSR5" not declared.
SQL>

The other commands you are trying to use are SQLPlus commands. The VARIABLE command declares a bind variable in SQLPlus -- it doesn't involve a DECLARE keyword at all. So you should try this:

VARIABLE csr1 REFCURSOR;
VARIABLE csr2 REFCURSOR;
VARIABLE csr3 REFCURSOR;
VARIABLE csr4 REFCURSOR;
VARIABLE csr5 REFCURSOR;
EXEC getReportData('PUB',:csr1,:csr2,:csr3,:csr4,:csr5); 
PRINT csr1;

I don't really understand the point of the rest of your question. A %ROWTYPE declaration doesn't mean that the record must be populated directly from the given table, just that its fields and their types matches those of a row from the table. These fields can be referenced and assigned just like any other variables.

Answer 2

You do not need the DECLARE if you are using SQL*Plus.

As you don't give us any details it is hard to know why your code isn't working. But this is the sort of thing you ought to be doing:

SQL> create or replace procedure getReportData
  2      (dno in dept.deptno%type
  3       , d_recs out sys_refcursor
  4       , e_recs out sys_refcursor)
  5  is
  6  begin
  7      open d_recs for
  8          select * from dept where deptno = dno;
  9      open e_recs for
 10          select * from emp where deptno = dno;
 11  end;
 12  /

Procedure created.

SQL> var rc1 refcursor
SQL> var rc2 refcursor
SQL>
SQL> exec getReportData (30, :rc1, :rc2)

PL/SQL procedure successfully completed.

SQL>
SQL> print rc1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL> print rc2

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7499 VAN WIJK   SALESMAN        7698 20-FEB-81       1600        300
        30

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

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


      7698 SPENCER    MANAGER         7839 01-MAY-81       2850
        30

      7900 HALL       CLERK           7698 03-DEC-81        950
        30

SQL>