Q
Problem solve Get help with specific problems with your technologies, process and projects.

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;

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.

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. Is there something I can do in my procedure or in my code to solve this problem? Thanks.

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.

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

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close