Q

Maximum open cursors exceeded

I've got two procedures in order to insert and append content to a BLOB field. 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. If the loop is executed too many times I get the message "ORA-01000: maximum open cursors exceeded ORA-06512 esql."

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.

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close