How to return and test a ref cursor
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How to return and test a ref cursor

  1. #1
    Join Date
    Jun 2000
    Location
    Bethel CT, USA
    Posts
    18

    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?

    egavish@snet.net

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Code:
    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.
    Ales
    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