I've got two procedures in order to insert and append content to a BLOB field:
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_MYTABLE values (nm_file, nr_seq, empty_blob() ) returning BL_FRAGMENT 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;
If the loop is executed too many times (the content is much more than 4,000 characters) I get the message:
ORA-01000: maximum open cursors exceeded ORA-06512 esql
I'm writing my code in esql language and the call for the procedure goes like this:
WHILE idx <= len DO SET piece = SUBSTRING(bytes FROM idx FOR step); SET query = 'CALL lob_pkg.add_more(''' || piece || ''')'; SET rs = PASSTHRU(query); SET idx = idx + step; END WHILE;
To solve the ORA-1000 error, look at increasing the OPEN_CURSORS initialization parameter for your database. The default value for this parameter can often be too low to support normal database operations.
If your database is using a static text pfile, then modify the pfile with a text editor. If your database is using the binary SPFILE, then modify the SPFILE's contents with the following command:
ALTER SYSTEM SET open_cursors=xxxx SCOPE=SPFILE;Where xxxx is a number larger than your current setting (which you can obtain by querying V$PARAMETER).
After your parameter file has changed, shut down and start up the database for the setting to take effect.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.