PL/SQLarraysv7

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