Reading from a ref cursor in PL/SQL -- How can I ?

From: laura pena <lizzpenaorclgrp@xxxxxxxxx>
To: Oracle-L@xxxxxxxxxxxxx
Date: Mon, 12 Dec 2005 13:44:36 -0800 (PST)

hi all,
  
  I have a function in sql that returns a reference cursor and I am wondering 
how I can read the output of this in PL/SQL?
  
  Here is my example function:
  
  CREATE OR REPLACE PACKAGE pkg_cursors AS
     TYPE ref_cursor IS REF CURSOR;
  END pkg_cursors;
  /
  
  
  CREATE OR REPLACE FUNCTION fu_example_ref_cursor  
   RETURN pkg_cursors.ref_cursor IS rt_calls pkg_cursors.ref_cursor; 
  --SQL> execute :ref :=  fu_example_ref_cursor();
  --SQL> print ref
  --COUNT(TABLE_NAME)
  -----------------
  --              120
  v_sqlstatement VARCHAR2(4000);
  BEGIN 
  
  
  v_sqlstatement := 'SELECT COUNT (table_name) from user_tables '; 
   OPEN rt_calls FOR  v_sqlstatement;
   return(rt_calls);
  END;
  /
  
  
  
  
  And here an example I can't get to work:
  CREATE OR REPLACE PROCEDURE pr_read_refCursor
  AS
  
  ret_ref pkg_cursors.ref_cursor; 
  
  BEGIN 
    DBMS_OUTPUT.ENABLE(1000000);
    DBMS_OUTPUT.PUT_LINE('example read ref cursor'); 
    ret_ref := fu_example_ref_cursor();
    
     DBMS_OUTPUT.PUT_LINE('output '|| ret_ref); 
  END;
  
  
  Thanks for any help.
  
  -Lizz
  

                        
---------------------------------
Yahoo! Shopping
 Find Great Deals on Holiday Gifts at Yahoo! Shopping

Reading from a ref cursor in PL/SQL -- How can I ?

Other related posts: