JL Computer Consultancy

Array processing in PL/SQL 8.0

May 1999


Historically PL/SQL has had limited value for large-scale row handling because of its inherent inefficiency and its limitation of single-row processing. This changed with the arrival of array processing in Oracle Version 8.0 (and has already changed again in 8.1)

This article is a brief demonstration of using array processing in PL/SQL, taking the example from the dbmssql script ($ORACLE_HOME/rdsbm/admin/dbmssql.sql) as a basis, and producing an array based equivalent.


rem
rem     Script:        pl_array.sql
rem     Author:        Jonathan Lewis
rem     Dated:         12th-April-1999
rem     Purpose:       Simple demo of pl/sql arrays in Oracle 8.0
rem
rem            This example is (vaguely) web-search related,
rem            demonstrating how to select rows and insert them
rem            into a holding table in order, but numbering them
rem            as they are inserted so that subsequent web-calls
rem            can ask for 'rows 12-22'.
rem
rem
rem     A table to use as the sample Ddta
rem
create table ob1 
unrecoverable
as
        select  name, 0 line_no 
        from    sys.obj$ 
        where   rownum <= 25;
rem
rem     rownum < 1 : my favourite way of creating an empty table
rem
create table ob2 as
        select  *
        from    ob1
        where   rownum < 1;
DECLARE
        src_cur        pls_integer;
        dest_cur       pls_integer;
        src_rows       pls_integer;
        dest_rows      pls_integer;
 
        -- Two of the new datatypes in the dbms_sql package
        -- An array of varchar2, and an array of number
 
        name_tbl       dbms_sql.varchar2_table;
        line_tbl       dbms_sql.number_table;
        array_size     number := 10;
begin
        for ct in 1..array_size loop
               line_tbl(ct) := ct;
        end loop;
        src_cur := dbms_sql.open_cursor;
        dbms_sql.parse(
                       src_cur, 
                       'select name from ob1 order by name', 
                       dbms_sql.V7
        );
        dest_cur := dbms_sql.open_cursor;
        dbms_sql.parse(
                       dest_cur,
                       'insert into ob2(name,line_no) values(:v1,:v2)',
                       dbms_sql.v7
        );
        --  Prepare the data from the source cursor
        src_rows := dbms_sql.execute(src_cur);
        loop
        -- Associate the name_tbl array with column 1 of the source cursor,
        -- and specify 'rows' 1 to 10 (array_size) as the boundaries of the
        -- section of the array that we want to use for the fetch.
 
               dbms_sql.define_array(src_cur, 1, name_tbl, array_size, 1);
               src_rows := dbms_sql.fetch_rows(src_cur);
        -- Exit if there were no more rows to fetch
               if (src_rows = 0) then
                       exit;
               end if;
        -- Move the values from the 1st column of the cursor
        -- into the name_tbl array
               dbms_sql.column_value(src_cur, 1, name_tbl);
        -- Associate the name_tbl array with bind-variable v1 of dest_cur,
        -- and limit the use of the array to be from 1 to the number of 
        -- rows fetched by the source cursor.
        -- Repeat to associate the line_tbl array with bind variable v2.
               dbms_sql.bind_array(dest_cur,'v1',name_tbl,1,src_rows);
               dbms_sql.bind_array(dest_cur,'v2',line_tbl,1,src_rows);
               dest_rows:=dbms_sql.execute(dest_cur);
        -- If the number of rows fetched filled the space we 
        -- specified, then there may be more to fetch, but if
        -- not, then we want to drop out now.
               if (src_rows < array_size) then
                       exit;
               end if;
        --  Bump up the line_tbl values by the size of the array we are
        --  using so that each row inserted gets a proper sequence number
               for ct in 1..array_size loop
                       line_tbl(ct) := line_tbl(ct) + array_size;
               end loop;
        end loop;
        dbms_sql.close_cursor(src_cur);
        dbms_sql.close_cursor(dest_cur);
end;
.
/
rem
rem     Take a look at the results
rem
select * from ob2;

Back to Main Index of Topics