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."

This Content Component encountered an error

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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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