Re: Ref Cursor

Alex wrote:
> I'm trying to execute a dynamic SQL statement using a ref cursor
> variable. The following is a simple example of what I'm trying to
> accomplish. I'd like to know how to declare the variable v_cur_dat to
> be able to accept any output, not only from table emp? Thanks in
> advance for your help.
>
> Alex
>
>
> DECLARE
>
> TYPE ref_cur_type IS REF CURSOR;
> v_cur ref_cur_type;
>
> v_cur_dat ??????
>
> BEGIN
> OPEN v_cur FOR SELECT * FROM emp;
>
> FETCH v_cur INTO v_cur_dat;
>
> END;
XML is one of several solutions (ANYDATASET and DBMS_SQL also come to mind). There are many function to extract data from the xmltype.

declare

     refcur sys_refcursor;
     xml xmltype;

begin

     open refcur for '
         select xmlforest(table_name, tablespace_name, num_rows)
         from user_tables';

     loop

         fetch refcur into xml;
         exit when refcur%notfound;

         dbms_output.put_line(xml.getstringval());

     end loop;

end;
/

--
Richard Kuhler