If you have created a table in PL/SQL, is there a way to send it back to SQL as a cursor - the answer in version 7 of Oracle was yes but a very inefficient yes. Following a few requests for more information I have now published a demonstration of how this work..
In Oracle 8, there is a very efficient way of achieving the same end, but only if you have the Object Option installed. This note (based on a newsgroup suggestion from Thomas Kyte) gives you three of the many possible variations on the basic method of casting a collection into a cursor,
First I can declare a type as a collection type as follows:-
create or replace type jpl_row as object( id number, description varchar2(40) ); / create or replace type jpl_array as table of jpl_row; /
So I have a type which is an array of objects. I can now populate a variable of this type with code of the following form:
declare my_table jpl_array:= jpl_array(); begin my_table := jpl_array( jpl_row(3,'Desc 3'), jpl_row(1,'Desc 1'), jpl_row(2,'Desc 2') ); end; /
If I can now produce a pure PL/SQL function returning a variable of this table type, I can take advantage of the fact that PL/SQL functions can be used in SQL statements, and do something like this:
SQL> select my_function from dual; NY_FUNCTION(ID, DESCRIPTION) ---------------------------------------------------------------------------- JPL_ARRAY(JPL_ROW(3, 'Desc 3'), JPL_ROW(1, 'Desc 1'), JPL_ROW(2, 'Desc 2'))
Once I have got this far, I can apply the CAST with the bizarrely named 'THE' operator to this returned type, to convert the collection into a cursor:
select id, description from the ( select cast(my_function as jpl_array) from dual ) order by id ;
to get:
ID DESCRIPTION --------- ---------------------------------------- 1 Desc 1 2 Desc 2 3 Desc 3
The following code fragments show possible implementation of this approach. The first version creates a set of packaged procedures to insert rows into a hidden table. The second makes the table public so that user code can append to it directly. The third exists simply to allow a user instantion of the correct type to be returned by a function.
One thing to be aware of - until Oracle 8.1 and it's declaration of variables as being passed by reference (NOCOPY), the array/table will be passed back and fore as a large object - this could use a lot of memory so you do have to trade off size of object against convenience of technique.
Code to create the various packages
rem rem Option 1 - hide the table completely. rem Use procedures to add a row, or rows to it rem The types are as declared above rem
create or replace package jpl_table as procedure initialise_table; procedure append_row(i_row in jpl_row); procedure append_some_rows(i_table in jpl_array); procedure append_many_rows(i_table in jpl_array); function return_table return jpl_array; pragma restrict_references(return_table, wnds, rnds, wnps); pragma restrict_references(jpl_table, wnds, rnds); end; /
create or replace package body jpl_table as
holding_table jpl_array := jpl_array();
procedure initialise_table is begin holding_table.delete; end;
function return_table return jpl_array is begin return holding_table; end;
procedure append_row (i_row in jpl_row) is begin holding_table.extend; holding_table(holding_table.last) := i_row; end;
procedure append_some_rows (i_table in jpl_array) is begin for i_ct in 1..i_table.count loop holding_table.extend; holding_table(holding_table.last) := i_table(i_ct); end loop; end;
procedure append_many_rows (i_table in jpl_array) is i_nn pls_integer; begin i_nn := holding_table.count; holding_table.extend(i_table.count); for i_ct in 1..i_table.count loop holding_table(i_nn + i_ct) := i_table(i_ct); end loop; end;
end jpl_table; . /
rem rem Option 2: Make the table visible in the package header, rem then insert into it more directly. rem
create or replace package jpl_table_2 as holding_table jpl_array := jpl_array(); function return_table return jpl_array; pragma restrict_references(return_table, wnds, rnds, wnps); end; /
create or replace package body jpl_table_2 as function return_table return jpl_array is begin return holding_table; end;
end; /
rem rem Option 3: Allow the users to create their own rem tables of the appropriate type, and simply offer rem a function that will return a supplied table so that rem is can be used in a SQL statement rem
create or replace package jpl_table_3 as function return_table (i_table in jpl_array) return jpl_array; pragma restrict_references(return_table, wnds, rnds, wnps); end; /
create or replace package body jpl_table_3 as function return_table (i_table in jpl_array) return jpl_array is begin return i_table; end;
end; /
Demo 1 - The table is hidden
begin jpl_table.initialise_table; jpl_table.append_row(jpl_row(3,'Desc 3')); jpl_table.append_row(jpl_row(1,'Desc 1')); jpl_table.append_row(jpl_row(2,'Desc 2')); end; /
select id, description from the ( select cast(jpl_table.return_table() as jpl_array) from dual ) order by id ;
begin jpl_table.append_some_rows( jpl_array( (jpl_row(5,'desc 5')), (jpl_row(4,'desc 4')) ) ); end; /
begin jpl_table.append_many_rows( jpl_array( (jpl_row(7,'desc 7')), (jpl_row(9,'desc 9')), (jpl_row(6,'desc 6')), (jpl_row(8,'desc 8')) ) ); end; /
select id, description from the ( select cast(jpl_table.return_table() as jpl_array) from dual ) order by id ;
Demo 2 - The table is publicly visible - the user understands how to add to it.
begin jpl_table_2.holding_table := jpl_array( jpl_row(3,'Desc 3'), jpl_row(1,'Desc 1'), jpl_row(2,'Desc 2') ); end; . /
select id, description from the ( select cast(jpl_table_2.return_table() as jpl_array) from dual ) order by id ;
Demo 3 - There is only a function to make the table usable - the user creates their own tables
variable x refcursor
declare my_table jpl_array:= jpl_array(); begin my_table := jpl_array( jpl_row(3,'Desc 3'), jpl_row(1,'Desc 1'), jpl_row(2,'Desc 2') );
open :x for select id, description from the ( select cast(jpl_table_3.return_table(my_table) as jpl_array ) from dual ) order by id ;
end; . /
print X