#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    99
    Rep Power
    11

    Unhappy 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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    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.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html

IMN logo majestic logo threadwatch logo seochat tools logo