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