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.

procedure pr_dup_bob_trun
as
stmt_str varchar2(50);
cur_hdl number;
rows_processed number;
begin
cur_hdl:=dbms_sql.open_cursor;
stmt_str:='truncate table dup_bob';
dbms_sql.parse(cur_hdl,stmt_str,dbms_sql.native);
rows_processed :=dbms_sql.execute(cur_hdl);
dbms_sql.close_cursor(cur_hdl);
end;

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???

Please Help...


Regards,
Sachin

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.