JL Computer Consultancy

PL/SQL arrays as Cursors (Oracle 7.3)

July 1999


If you have created an array in PL/SQL is there a way to send it back to SQL as a cursor in version 7 of Oracle ? The answer is yes but it's very inefficient. The following package gives you an example of how the method works. A much better method is available if you have Oracle 8 with the Object option.

There are two steps in Oracle 7.3. First you need some way of selecting an arbitrarily long list of integers with no gaps. You could do this with a silly statement like 'select rownum from all_objects', but I prefer to create a limited table of one column which I populate with a one-off SQL statement. e.g:

               create table sequential_numbers
               unrecoverable
               pctfree 0
               as
               select rownum n from all_objects
               ;

You then need to write a package with a function to populate the array, a function to count the number of rows in the array, and a function to return one 'column' from each row in the array. Once you have these, you can write an SQL script like:

               
               variable tab_size number
               execute :tab_size := array_as_cursor.populate_array;
               select
                       n,
                       array_as_cursor.number_column(n),
                       substr(array_as_cursor.varchar2_column(n),1,20)
               from
                       sequential_numbers
               where n <= :tab_size
/*             where n <= (select array_as_cursor.current_size from dual)   */
               ;

As you can see the method is a little clumsy, and for every item of data you want you have to call a function to get a value - the CPU cost is quite dramatic, so don't use this method for large amounts of data.

Note: if you don't want to use the bind variable method then you MUST use a subquery select from dual to return the current size just once, otherwise a construct like 'n <= array_as_cursor.current_size' will result in the function being called once for every row in the sequential_numbers table.

Note also the substr() function used on the varchar2 function - pl/sql functions do NOT return bounded values so the default return column is assumed to be VARCHAR2(2000), similarly the returned number column is assumed to be just NUMBER, and not NUMBER(p,s). You need to impose your own bounds in the SQL itself (although you could protect end-users by creating a view to impose these bounds)..


The demonstration package

rem
rem     Script:        c_pack.sql
rem     Dated;         July-1999
rem     Author:        Jonathan Lewis
rem     Oracle:        7.3.3.5
rem     Purpose:       Package to demonstrate arrays returned by cursors in v7
rem
rem     Notes:
rem     This is very CPU intensive, and should 
rem     be used only for very small sets of data.
rem
rem     To stick to the lowest version of PL/SQL, it avoids using
rem     records for the array. Also, because Oracle Corp. forgot to put
rem     the 'restrict_references' pragma into the definitions of
rem     functions used to count arrays, the method for counting
rem     the content of the array is somewhat naive.
rem
rem     It would be nice to be able to select where
rem
rem            n <= (select array_as_cursor.populate from dual)
rem
rem     to populate the array and return the count in one step, 
rem     but in recent versions of PL/SQL this cannot be done as the
rem     function used in the sub-query may not write a package state
rem     (WNPS) so it cannot populate the array.  (This check is
rem     missed, I think, in earlier versions of PL/SQL:  a function
rem     could  write its OWN package state and still be given the 
rem     purity level WNPS without causing a compile error).  You can
rem     also do this in Oracle 8.1 - but there are better ways of
rem     turning arrays into cursors in 8.1 anyway.
rem
create or replace package array_as_cursor as
        function populate_array return number;
        pragma restrict_references(populate_array, wnds); 
        function current_size return number;
        pragma restrict_references(current_size, wnds, rnds, wnps); 
        function number_column(i in integer) return number;
        pragma restrict_references(number_column ,wnds, rnds ,wnps);
        function varchar2_column(i in integer) return varchar2;
        pragma restrict_references(varchar2_column, wnds, rnds, wnps);
end;
/
create or replace package body array_as_cursor as
type    t_numbers      is table of number index by binary_integer;
type    t_varchar2s    is table of varchar2(2000) index by binary_integer;
v_numbers      t_numbers;
v_empty_numbers t_numbers;
v_varchar2s            t_varchar2s;
v_empty_varchar2s      t_varchar2s;
v_count number := 0;
function populate_array return number is
begin
        v_varchar2s := v_empty_varchar2s;
        v_numbers := v_empty_numbers;
        v_count := 0;
--      Do what you want to fill arrays here e.g.
        for r in (
                       select table_name, num_rows 
                       from user_tables 
                       where rownum <= 5
        ) loop
               v_count := v_count + 1;
               v_numbers(v_count) := r.num_rows;
               v_varchar2s(v_count) := r.table_name;
        end loop;
        return v_count;
end;
        
function current_size return number is
begin
        return v_count;
end;
function number_column(i in integer) return number is
begin
        return v_numbers(i);
exception
        when no_data_found then
               return null;
end;
function varchar2_column(i in integer) return varchar2 is
begin
        return v_varchar2s(i);
exception
        when no_data_found then
               return null;
end;
end;
/
 

Output from the demonstration SELECT statement above

PL/SQL procedure successfully completed.
        N ARRAY_AS_CURSOR.NUMBER_COLUMN(N) SUBSTR(ARRAY_AS_CURS                                     
--------- -------------------------------- --------------------                                     
        1                               33 CUSTOMER                                                 
        2                               11 DEPARTMENT                                               
        3                               32 EMPLOYEE                                                 
        4                              271 ITEM                                                     
        5                                6 JOB                                                      

Back to Main Index of Topics