Returning a cursor from an inner procedure to outer procedure in oracle pl/sql

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