Re: How to Pass Large Amounts of Data Through PL/SQL Procedure Arguments?

From: Billy <vslabs_at_onwe.co.za>
Date: 25 Jul 2005 22:49:39 -0700
Message-ID: <1122356979.309307.266140@f14g2000cwb.googlegroups.com>

belda2010_at_hotmail.com wrote:
> Either LOBs or LONG type arguments are not working when I pass them
> through parameters with values exceeding 32K.
> The problem is not concerning LOB handling but with passing large
> amount of data, data over 32K, through pl/sql procedure argument (or
> parameter) no matter of the argument type.

Then you have a bug in your code. The following is from a 9i SQl*Plus session, showing a 10MB CLOB being passed to a stored proc: ==
SQL> create or replace procedure foolob( big IN OUT NOCOPY CLOB ) is   2 i integer;
  3 begin

  4          i := DBMS_LOB.getLength( big );
  5          W( 'CLOB: '||i||' char(s)' );
  6          W( 'CLOB: '||i/1024||' KB (assuming singlebyte charset)'

);
  7 end;
  8 /

Procedure created.

SQL>
SQL>
SQL> declare
  2          c       clob;
  3          i       integer;
  4          buf     varchar2(1024);
  5          s       number;
  6  begin
  7          buf := RPAD('*',1024,'*');
  8          s := 0;
  9          DBMS_LOB.createTemporary( c, FALSE );
 10
 11          for i in 1..10240
 12          loop
 13                  DBMS_LOB.writeAppend( c, length(buf), buf );
 14                  s := s + length(buf);
 15          end loop;
 16
 17          W( s||' char(s) written into CLOB' );
 18          foolob( c );

 19
 20
 21 DBMS_LOB.freeTemporary( c );  22 end;
 23 /
10485760 char(s) written into CLOB
CLOB: 10485760 char(s)
CLOB: 10240 KB (assuming singlebyte charset)

PL/SQL procedure successfully completed.

SQL>
==

--
Billy

Received on Tue Jul 26 2005 - 00:49:39 CDT