4

I am using oracle PL/SQL procedure. I am calling one procedure inside another. I want to return a cursor from the nested procedure to the outer procedure. Is this possible? How adversely does it affect the procedure?

Below is the calling structure:

  Proc1( data1 IN integer, cursor1 OUT SYS_REFCURSOR ) {
       Proc2(data2 IN , cursor1 out) {
           open cursor1 FOR
           select * from table;
       }
  }
General Grievance
  • 4,555
  • 31
  • 31
  • 45
Tito
  • 8,894
  • 12
  • 52
  • 86

1 Answers1

6

Here is one example of calling procedures that have REF CURSOR OUT parameters.

SQL> create or replace procedure p1(
  2    p_empno in emp.empno%type,
  3    p_rc   out sys_refcursor
  4  )
  5  as
  6  begin
  7    open p_rc
  8     for
  9     select *
 10       from emp
 11      where empno = p_empno;
 12  end;
 13  /

Procedure created.

SQL> create or replace procedure p2(
  2    p_empno  in emp.empno%type,
  3    p_rc    out sys_refcursor
  4  )
  5  as
  6  begin
  7    p1( p_empno, p_rc );
  8  end;
  9  /

Procedure created.

In this case, I'm creating a SQL*Plus substitution variable rc in order to demonstrate how to call p2. If you are calling it in something other than SQL*Plus, the syntax will be a bit different but the general principle will be the same.

SQL> var rc refcursor;
SQL> exec p2( 7900, :rc );

PL/SQL procedure successfully completed.

SQL> print rc

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO   FAKE_COL        FOO
---------- ---------- ----------
      7900 SM2        CLERK           7698 03-DEC-81        950
        30          1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • thank u . does this kind of calling calling have any performance issues. does it affect the procedures adversely ? – Tito Jan 12 '11 at 06:39
  • @tito - A REF CUSROR is just a pointer, so the overhead of passing a REF CURSOR between different procedures is pretty minimal. The performance penalty would come if you are using REF CURSORs incorrectly in the first place-- if you are writing nested loops, for example, rather than letting Oracle handle joins or if you are doing lots of single-row fetches from a REF CURSOR rather than doing a BULK COLLECT, there are likely opportunities for optimization. – Justin Cave Jan 12 '11 at 19:49