JL Computer Consultancy

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