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

Mark Frost wrote:
>
> 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.

You could look at the NOCOPY option, and fyi, tables of tables are permitted (once you get to 9i)

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."