JL Computer Consultancy

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                                                      
复制