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

I think I got it :
  CREATE OR REPLACE PROCEDURE pr_read_refCursor
  AS
  
  p_retcur SYS_REFCURSOR;
  v_rec NUMBER;
  
  BEGIN 
    DBMS_OUTPUT.ENABLE(1000000);
    DBMS_OUTPUT.PUT_LINE('example read ref cursor'); 
    p_retcur := fu_example_ref_cursor();
    FETCH p_retcur INTO v_rec;
    
     DBMS_OUTPUT.PUT_LINE('output '|| v_rec); 
  END;
  
  
  Let me know if you disagree.
  
  -Lizz
  

laura pena <lizzpenaorclgrp@xxxxxxxxx> wrote:  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 


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

Other related posts: