JL Computer Consultancy

PL/SQL tables as Cursors (Oracle 8.0)

June 1999


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.

Back to Main Index of Topics


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

Back to Main Index of Topics