Ask the Expert

Maximum open cursors exceeded

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;
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 esql
Is 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.

    Requires Free Membership to View

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.

This was first published in June 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: