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

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Mon, 25 Jul 2005 21:49:09 +0000 (UTC)
Message-ID: <dc3mol$u3t$1@klatschtante.init7.net>

On 2005-07-25, belda2010_at_hotmail.com <belda2010_at_hotmail.com> wrote:
> Hi,
>
> I have a problem when passing values greater than 32K througn pl/sql
> procedure arguments with Oracle 9i.
>
> How can I do to pass large amount of data through pl/sql procedure
> argument and go over this limit?

Something like the following perhaps?

create or replace procedure pass_clob (param in clob) is begin   dbms_output.put_line('length of lob: ' || dbms_lob.getlength(param));   null;
end;
/

set serveroutput on

declare
  p clob;
begin
  dbms_lob.createtemporary(p, false,dbms_lob.call);   dbms_lob.open(p, dbms_lob.lob_readwrite);   for i in 1..2**20/4096 loop
    dbms_lob.writeappend(p, 4096, utl_raw.cast_to_raw(lpad('x', 4096, 'x')));   end loop;

  pass_clob(p);
  dbms_lob.close(p);
  dbms_lob.freetemporary(p);

end;
/

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Mon Jul 25 2005 - 16:49:09 CDT