DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:to:subject:mime-version:content-type;
        b=d9/uNELT3eRQDdo0SRUwiwDdSuXBY4pyM4QXWyvTZIIS6id86KFHrZ/Y6nstTFCm0/POTtg2jyWM5MJhSZI1rEd0uIYm8kMWN1dU0XR+qx6El2NEeoMcvZ0s6Nk3JVSMIC0xTb4+19YhHjmr+sH5lGRhVTG5NoMrjvhhp8FBmo4=
Message-ID: <faf909c50606060639q39c299c4x47f425fb5e7a5b2f@mail.gmail.com>
Date: Tue, 6 Jun 2006 10:39:53 -0300
From: "Raphael Silva" <raphael.silva@gmail.com>
To: oracle-l@freelists.org
Subject: Inserting more than 4000 characters into BLOB field
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----=_Part_14006_12899824.1149601193647"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: raphael.silva@gmail.com
------=_Part_14006_12899824.1149601193647
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

In order to insert more than 4000 characters into the BLOB field,
I created 2 procedures:

CREATE OR REPLACE package body lob_pkg
as

g_blob blob;


procedure lob_ins( nm_file in varchar2, nr_seq in number, p_text in raw)
as
begin
     insert into TB_FRAG values (nm_file, nr_seq, empty_blob() ) returning
PCCF_BL_FRAG into g_blob;
     dbms_lob.write( g_blob,utl_raw.length(p_text), 1,p_text);
end;

procedure add_more( p_text in raw )
as
begin
    dbms_lob.writeappend( g_blob, utl_raw.length(p_text), p_text );
end;

end;

So, in my program, first i make the call to the first procedure (lob_ins)
and than from 4000 to 4000 characters i call recursively the second one,
till all the characters have been appended to the blob.

The problem is that i'm getting the max open cursor error.
I tried to set a higher number of cursors but it was no use.
I want to know if there is some other way to insert stuff into the Blob
field, or if there is something wrong with my procedures.

Thanks,
Raphael

------=_Part_14006_12899824.1149601193647
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

In order to insert more than 4000 characters into the BLOB field,<br>I created 2 procedures:<br><br>CREATE OR REPLACE package body lob_pkg<br>as<br><br>g_blob blob;<br><br><br>procedure lob_ins( nm_file in varchar2, nr_seq in number, p_text in raw)
<br>as<br>begin<br>&nbsp;&nbsp;&nbsp;&nbsp; insert into TB_FRAG values (nm_file, nr_seq, empty_blob() ) returning PCCF_BL_FRAG into g_blob;<br>&nbsp;&nbsp;&nbsp;&nbsp; dbms_lob.write( g_blob,utl_raw.length(p_text), 1,p_text);<br>end;<br><br>procedure add_more( p_text in raw )
<br>as<br>begin<br>&nbsp;&nbsp;&nbsp; dbms_lob.writeappend( g_blob, utl_raw.length(p_text), p_text );<br>end;<br><br>end;<br><br>So, in my program, first i make the call to the first procedure (lob_ins) and than from 4000 to 4000 characters i call recursively the second one, till all the characters have been appended to the blob.
<br><br>The problem is that i'm getting the max open cursor error.<br>I tried to set a higher number of cursors but it was no use.<br>I want to know if there is some other way to insert stuff into the Blob field, or if there is something wrong with my procedures.
<br><br>Thanks,<br>Raphael<br>

------=_Part_14006_12899824.1149601193647--
--
http://www.freelists.org/webpage/oracle-l