Re: REf cursors on Pl/SQL tables

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 8 Jan 2002 16:30:48 -0800
Message-ID: <a1g2vo02rv8@drn.newsguy.com>

In article <pfF_7.1072$yw1.5671_at_news.uk.colt.net>, "Richard says...
>
>Anyone know if it is possible to use cursors against pl/sql tables in stored
>procedures?
>
>What I want to do is populate a pl/sql table, iterate through it, making
>some changes and then open a refcursor against it to be returned as an
>output parameter from the stored procedure.
>
>
>appreciate the help.
>
>Richard
>
>

Here is how:

ops$tkyte_at_ORA8I.WORLD> create or replace type myScalarType as object   2 ( x int, y date, z varchar2(20) )
  3 /

Type created.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> create or replace type myTableType as table of myScalarType;
  2 /

Type created.

ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> variable x refcursor
ops$tkyte_at_ORA8I.WORLD> set autoprint on
ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> declare
  2          l_x     myTableType := myTableType
                            ( myScalarType( 1, sysdate, 'hello' ),
  3                           myScalarType( 2, sysdate, 'bye' ) );
  4  begin
  5          open :x for
  6          select * from TABLE( cast( l_x as myTableType ) );

  7 end;
  8 /

PL/SQL procedure successfully completed.

         X Y Z

---------- --------- --------------------
         1 08-JAN-02 hello
         2 08-JAN-02 bye

the types MUST be sql types -- not defined in a package spec -- defined in SQL as i did above. repeat - not in a spec.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Jan 08 2002 - 18:30:48 CST