How to return and test a ref cursor

[egavish]

Need a short example:

I have a package with declaration of cursor, cursor type, and stored procedure. I have the package body with the stored procedure which returns a ref cursor (output)

My question:

How do I code a sample call and test the results of the stored procedure (ref cursor) from SQLPLUS?

egavish@snet.net

[ales]

scott@oracle> create or replace package test as
  2  type testcursor is ref cursor;
  3  procedure testcur(output out testcursor);
  4  end;
  5  /

Package created.

scott@oracle> create or replace package body test as
  2  procedure testcur(output out testcursor) is
  3     begin
  4       open output for select empno, ename from emp;
  5     end;
  6  end;
  7  /

Package body created.

scott@oracle> var c refcursor
scott@oracle> exec test.testcur(:c)

PL/SQL procedure successfully completed.

scott@oracle> print c

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.