Passing PL/SQL tables around in stored procedures is very slow

From: Mark Frost <mfrost_at_hemscott.co.uk>
Date: 12 Nov 2001 06:34:35 -0800
Message-ID: <87bccb62.0111120634.76b4a3eb@posting.google.com>

I have a stored procedure that will build XML data and return to a JDBC client. If I populate the PL/SQL table within my routine everything is fine and is very quick (approx 500ms for 0.5 Mb of XML data). The test/example code is as follows:

CREATE OR REPLACE PACKAGE Utils
IS

	SUBTYPE BLOCK_T IS VARCHAR2(2048);
	TYPE BUFFER_T IS TABLE OF BLOCK_T INDEX BY BINARY_INTEGER;

END Utils;
/

CREATE OR REPLACE PROCEDURE Procout_Strings (p1 OUT Utils.buffer_t) IS

	temp Utils.block_t;
	BEGIN
		FOR i IN 1..30
		LOOP
			temp := temp || '0123456789';
		END LOOP;

		FOR i IN 1..2000
		LOOP
			p1(i) := temp;
		END LOOP;
	END;

/

However, I wish to "build" the XML within a utility procedure - basically I want to pass the PL/SQL table around. If I do this the time degrades to over 15 seconds!! The bottleneck is not with JDBC (it runs slow if called from PL/SQL directly). The code is as follows:

CREATE OR REPLACE PROCEDURE Test(buffer IN OUT Utils.BUFFER_T, string IN OUT VARCHAR2)
AS
BEGIN
         buffer(buffer.COUNT + 1) := string;
END;
/

CREATE OR REPLACE PROCEDURE Procout_Strings (p1 OUT Utils.buffer_t) IS

	temp Utils.block_t;
	BEGIN
		FOR i IN 1..30
		LOOP
			temp := temp || '0123456789';
		END LOOP;

		FOR i IN 1..2000
		LOOP
			test(p1, temp);
		END LOOP;
	END;

/

Does anyone know why this runs so slowly when the table is passed around? I have defined it as IN OUT so surely its just passing a reference?

I've tried using a globally defined PL/SQL table. This runs fast but obviously I would need an "array" of such tables (you cannot have tables of tables it seems).

Is their another way of achieving my goal. Basically returning character data to JDBC, built from a PL/SQL procedure, the size of which can be greater than the 32k limit on a VARCHAR2.

I am using Oracle 8.1.7 on Solaris.

Many thanks. Received on Mon Nov 12 2001 - 08:34:35 CST