ref cursors truly are pointers, and just like in C -- if you have a function that takes the address of an address and assigns a new address to it -- you'd lose what the old address points to! ref cursors are "in out" type of things. They get pointed TO a result set, it is not that a result set is copied into whereever they are pointing.

What we can do is use a piplined function -- to have the outerproc return rows from the ref cursors from inner proc as soon as they are generated. It would look something like this:



ops$tkyte@ORA9IR2> create or replace type myScalarType as object
2 ( x int, y date, z varchar2(30) )
3 /

Type created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace type myArrayType as table of myScalarType
2 /

Type created.

think of those as your "view", think of them as you would "create view", they are just definitions -- yes, you must have them


ops$tkyte@ORA9IR2> create or replace package demo_pkg
2 as
3 procedure a ( p_input in number, p_cursor in out sys_refcursor );
4 function b return myArrayType PIPELINED;
5 end;
6 /
Package created.

that'll let us select * from that plsql function..

ops$tkyte@ORA9IR2> create or replace package body demo_pkg
2 as
3 procedure a ( p_input in number, p_cursor in out sys_refcursor )
4 is
5 begin
6 open p_cursor for
7 select user_id, created, username
8 from all_users
9 where user_id = p_input;
10 end;
11
12 function b return myArrayType PIPELINED
13 is
14 l_cursor sys_refcursor;
15 l_data myScalarType := myScalarType( null, null, null );
16 begin
17 for x in ( select * from all_users where rownum < 8 )
18 loop
19 a( x.user_id, l_cursor );
20 loop
21 fetch l_cursor into l_data.x, l_data.y, l_data.z;
22 exit when l_cursor%notfound;
23 pipe row ( l_data );
24 end loop;
25 close l_cursor;
26 end loop;
27 return;
28 end;
29 end;
30 /
Package body created.

Line 23 is the magic, it sends the row back to the client -- right away (client gets the first row from this process way before we process the last row in the plsql procedure -- it "pipes" the data...)



ops$tkyte@ORA9IR2> select * from table( demo_pkg.b );

instead of calling demo_pkg.b to get a ref cursor, client opens the cursor themselves -- invoking demo_pkg.b that way...



X Y Z
---------- --------- ------------------------------
0 18-SEP-04 SYS
5 18-SEP-04 SYSTEM
11 18-SEP-04 OUTLN
19 18-SEP-04 DBSNMP
49 24-SEP-04 SCOTT
21 19-SEP-04 WMSYS
30 19-SEP-04 ORDSYS

7 rows selected.