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

From: laura pena <lizzpenaorclgrp_at_yahoo.com>
Date: Mon, 12 Dec 2005 13:53:18 -0800 (PST)
Message-ID: <20051212215318.58347.qmail@web35002.mail.mud.yahoo.com>

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_at_yahoo.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Dec 12 2005 - 16:06:58 CST