DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2000
    Bethel CT, USA

    How to return and test a ref cursor

    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?


  2. #2
    Join Date
    Apr 2001
    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.
    The whole difference between a little boy and an adult man is the price of toys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.