I've got two procedures in order to insert and append content to a BLOB field:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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;In my code I call these procedures to insert content bigger than 4,000 characters. I call the second procedure add_more( p_text in raw ) in a loop, passing 4,000 characters each time.
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 esqlIs there something I can do in my procedure or in my code to solve this problem?
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;Thanks.
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.