The Jonathan Lewis Pages

Array processing in PL/SQL 8.0

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