Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Passing PL/SQL tables around in stored procedures is very slow

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

From: Jim Kennedy <kennedy-family_at_home.com>
Date: Mon, 12 Nov 2001 16:18:45 GMT
Message-ID: <FDSH7.16883$XJ4.11621277@news1.sttln1.wa.home.com>


Try the nocopy option. I tis probably copying the table everywhere instead of passing a pointer.
Jim
"Mark Frost" <mfrost_at_hemscott.co.uk> wrote in message news:87bccb62.0111120634.76b4a3eb_at_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 - 10:18:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US