January 14th, 2004, 02:06 AM
Too Many Cursors Error
I am using two types of cursors in my System Oracle 9i. One is a "For" Cursor and Other Cursor is a dbms.type Cursor as given below.
stmt_str:='truncate table dup_bob';
Even though all the Cursors are closed... Sometimes (One in 3-4 Months) it gives the following
"ERROR Occured at ORA-00604 Error Occured at recursive SQL Level ORA-01000 Maximum Open Cursor Exceeded"
I have checked all the Procedures thoroughly for any open cursors but there are none.
Does "For" Cursors have to be manually Closed??? Also What Happens When There is an Exception in the For Cursor that are Not handled, Does the Cursor remain open???
January 15th, 2004, 09:37 AM
Oracle opens cursors implicitly. What is occurring is that the cursor resources are not being released and returned fast enough when the demand is high. You can modify this at the DBMS level but I would advise against that. You can also attempt to redefine your code to be more streamlined and utilized open resources.