Forum Stats

  • 3,750,349 Users
  • 2,250,160 Discussions
  • 7,866,946 Comments

Discussions

Cannot figure out why "ORA-01000 Maximum open cursors" is shown...

bingo333
bingo333 Member Posts: 14
edited May 15, 2013 6:34AM in SQL & PL/SQL
Hello there ...

I am programming a PL/SQL Code that is throwing 0RA-01000 Maximum Open Cursors Exceeded.

Having already read quite a lot about ORA-01000 errors, I know I should be closing cursors, and have already tried setting OPEN_CURSORS parameter to a high number (1000).

I declared a lot of procedures in my pl/sql, each of which uses one cursor since i am working with a non-Oracle table linked by ODBC ... and each procedure sometimes does thousands of inserts -- but all WITHIN the explicit cursors. The explicit cursors are not declared within each loop.

I already checked the code many times, and made sure all open cursors are closed. In addition, I also verified the numberopen cursors generated by the PL/SQL by running the following SQL after every procedure i run... and outputting it... and it appears the value just keeps on increasing, even though I had explicitly closed all the cursors in all the earlier procedures.

What is funny is that the most number of cursors reported by the code below only hits 150+ cursors. Nowhere near the 1000 open_cursors limit per session.
select a.value into strtxt --, b.name         
        from v$mystat a, v$statname b
        where a.statistic# = b.statistic#
        and a.statistic#= 3;
When I run the procedures separately though, all the procedures run smoothly (even when I had not yet updated the open_cursors parameter).

I was thinking of the following, but maybe you have some other ideas?
Does this have anything to do with my procedures not being stored procedures?
Or should i be committing records within my procedures instead of out of it?

I really have run into a wall and would really appreciate any tips or helps on this. Thanks in advance!

My basic pl/sql code looks like below. I did not give the actual details cause it will be too long (up to 5000 lines).
DECLARE 
...

PROCEDURE proc1
IS

    CURSOR cur_hca
       is 
           select ...from..where;
   TYPE cur_hca_fetch
        Is TABLE OF cur_hca%ROWTYPE
            INDEX BY PLS_INTEGER;
            
    temp_collect cur_hca_fetch;
 ... 
BEGIN
 ...  
   open cur_hca;         --cur_hca is the cursor name.
                                  --i use exactly the same cursor name in the other procedures
      ...
      loop
         fetch cur_hca bulk collect into temp_collect LIMIT 1000;
         exit when temp_collect.count=0
  
         for indx in 1 .. temp_collect.count
            loop
              ...run some sql
            end loop;
      end loop;
  close cur_hca;
END proc1;

PROCEDURE proc2   --almost the same as above the only changes are the query for the 
                             -- cursor and the sql that happens for each record
IS
 ... 
BEGIN
 ...  
   open cur_hca;         --cur_hca is my cursor name
      ...
      loop
       
         ...
      end loop;
  close cur_hca;
END proc2;

... up to 40 other very similar procedures


BEGIN
   proc1;
   commit;

   select a.value into strtxt 
        from v$mystat a, v$statname b
        where a.statistic# = b.statistic#
        and a.statistic#= 3;
  DBMS_OUTPUT.PUT_LINE('Number of Cursors After STATUSproc1: ' || strtxt);  


   proc2;
   commit;
   select a.value into strtxt 
        from v$mystat a, v$statname b
        where a.statistic# = b.statistic#
        and a.statistic#= 3;
   DBMS_OUTPUT.PUT_LINE('Number of Cursors After STATUSproc2: ' || strtxt);  
   ...
   ...
   ... 40 other procedures

END;
Edited by: user4872285 on May 6, 2013 6:49 PM

Edited by: user4872285 on May 6, 2013 7:01 PM

Edited by: user4872285 on May 6, 2013 8:02 PM

Edited by: user4872285 on May 6, 2013 8:03 PM
Tagged:
«1

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    does PL/SQL code contain any EXCEPTION handler?


    How do I ask a question on the forums?
    2176552
  • bingo333
    bingo333 Member Posts: 14
    edited May 6, 2013 10:17PM
    Hi there,
    Thanks for the quick reply!! Appreciate it...

    No, I did not put an exception handler in the PL/SQL code.

    Should I? I didn't think I needed to because I was developing the code with known data and there shouldn't have been any errors because i tried out each procedure code separately and they worked properly.

    Thanks so much! if you have other ideas i'd be happy to hear them too. Thanks!!


    Edited by: user4872285 on May 6, 2013 7:03 PM

    Edited by: user4872285 on May 6, 2013 7:17 PM
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    NO, do NOT add or have any EXCEPTION handler!
  • bingo333
    bingo333 Member Posts: 14
    Oh okay, I won't put in exception handlers then.

    Anyway, just to add to what is keeping me stumped, I tried adding the following lines below to each of my procedures after I run them...just to doublecheck if I had closed my procedures or not.

    And the DBMS output is showing that the "cursor was closed"!!
     if cur_hca%ISOPEN then
        close cur_hca;
        DBMS_OUTPUT.PUT_LINE('cursor was open);
      else
        DBMS_OUTPUT.PUT_LINE('cursor was closed);  
      end if;  
    I will try to make the procedures all stored procedures, just to see if this will have the same effect.
    As opposed to just declaring the procedures within my main PL/SQL block.

    Thanks again...
  • bingo333
    bingo333 Member Posts: 14
    Just an update:
    I have tried taking out all the 40+ procedures from the main PL/SQL block, and doing "create or replace" so that they become stored procedures.
    However, the error still remains. :(

    So, i guess that means the location of the procedure (whether as a declared procedure or a stored procedure) has no bearing on the cursor usage (as i had suspected).
  • bingo333
    bingo333 Member Posts: 14
    Just another update ...

    I was just thinking through this problem yesterday ... and am beginning to wonder if this is some sort of Oracle bug ...

    The thing is, I have already run the different procedures individually, to load millions of records into the linked MySQL database--without any errors. So those procedures I have been calling can't be possibly the ones leaking cursors... otherwise I would have encountered the "Maximum open cursors exceeded" error message during those procedures already.

    It is only when I sequentially call these procedures in another PL/SQL script that the error gets thrown.

    Maybe Oracle does not like such multiple calls of procedures in PL/SQL?

    Has anyone encountered this kind of thing before? Anyways, it would be great to hear your thoughts or suggestions.

    Thanks so much.
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    As a sanity test, consider testing against another Oracle DB to see what results.
    sb92075
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,570 Red Diamond
    PL/SQL code usually leaks reference cursors and DBMS_SQL cursors - as the ref cursor/DBMS_SQL interface used has a global (session static) scope.

    PL/SQL has an intelligent garbage collector that will close local implicit and explicit cursors, when the cursor variable goes out of scope.

    If you define an explicit cursor globally (package interface), then it can only be opened once. The 2nd attempt results in a ORA-06511: PL/SQL: cursor already open exception. So code cannot leak explicit cursors as code cannot reopen an existing opened explicit cursor.

    I have never seen Oracle leaking cursors internally. So I would be hesitant to call what you are seeing, a bug. If your code is using explicit cursors (even static/global ones), your code cannot leak these cursors, even if your code does not close them. Worse case - the cursor remains open, however new copies cannot be created while it is open.

    So I think your are looking at the wrong thing - explicit cursors. These are not the cursors that are leaking in my view (simply because code cannot reuse and open an already opened explicit cursor). Here is an example:
    SQL> show parameter cursors
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    open_cursors                         integer     300
    session_cached_cursors               integer     50
    
    // procedure that seems to "leak" an explicit cursor handle
    // as it does not explicitly closes the handle
    SQL> create or replace procedure CursorUse is
      2          cursor c is select e.* from emp e;
      3          empRow  emp%RowType;
      4  begin
      5          open c;
      6          fetch c into empRow;
      7          --// not closing explicit cursor handle
      8          --// and going out-of-scope
      9  end;
     10  /
    
    Procedure created.
    
    // current session stats
    SQL> select b.name, a.value from v$mystat a, v$statname b where a.statistic# = b.statistic# and b.name like '%open%cursor%';
    
    NAME                                  VALUE
    ------------------------------ ------------
    opened cursors cumulative                91
    opened cursors current                    2
    
    // execute proc that "leaks" a cursor, 10000 times
    SQL> begin
      2          for i in 1..10000 loop
      3                  CursorUse;
      4          end loop;
      5  end;
      6  /
    
    PL/SQL procedure successfully completed.
    // no errors due to cursor leakage
    
    // session stats: no cursor leakage occurred as
    // PL/SQL's garbage collector cleaned (and closed)
    // cursor handles when these became out-of-scope
    SQL> select b.name, a.value from v$mystat a, v$statname b where a.statistic# = b.statistic# and b.name like '%open%cursor%';
    
    NAME                                  VALUE
    ------------------------------ ------------
    opened cursors cumulative            10,095
    opened cursors current                    2
    
    SQL> 
    So the cursor leakage you are seeing is caused by something else... so what else is part of the code, or the session, that you have not yet mentioned?
    Billy Verreynne
  • bingo333
    bingo333 Member Posts: 14
    edited May 14, 2013 2:52AM
    Dear Billy and sb92075,

    Thanks so much for your answers.

    I've tried to think through your comments and suggestions, but I cannot really think of any other way the code could be leaking cursors ... As I mentioned, each of the codes executed by themselves could literally load millions of records, and so each of them alone cannot be the culprit.

    And, the final PL/SQL code IS as simple as I described in my original post. Before I added the other queries to check how many cursors are open, the basic PL/SQL code really just looked like the code below...which is what caused me to be stumped. haha...
    BEGIN
         proc1;
         commit;
         proc2;
         commit;
         proc3;
         commit;
         ... up to proc40
    END;
    And each of the procedures was just copying something from an Oracle database to the linked MySQL database or at worst, just adding a few if ... then ... else statements along the way...and I made sure to close each and every explicit cursor.

    As you described, this could be something else. If you say that Oracle doesn't leak cursors internally, then my best guess at the moment is that it could be the MySQL client or the unixODBC connection doesn't close things neatly? Or maybe that Oracle's hsodbc program has a problem (we are using Oracle10g and no dg4odbc yet).

    Anyway, since I was running out of time (as other stuff keeps piliing on) and I can't test exactly what is the cause... what I just did was to just work around the problem ... and I just called each of the procedures sequentially in an external program, instead of calling everything from within a single PL/SQL statement. It's not as elegant a solution as I hoped, but ugly is better than not working altogether.

    Anyways, thanks again for your inputs!! It helped me think through the problem...giving me ideas for the workaround.

    But, if anyone else encounters this in the future, it would be interesting to see if someone else finds a solution or a reason why this thing comes up.

    Edited by: bingo333 on May 13, 2013 11:42 PM
  • bingo333
    bingo333 Member Posts: 14
    OH and another thing I was thinking of but never got to try because I ran out of time was to change the name of the cursor for each procedure.

    I used the same cursor name for each of the procedures. (Precisely because each of the procedures are so similar I didn't need to change much).

    Anyway, I am posting this just in case someone encounters this in the future, and will use my experience to their benefit.

    Thanks and regards,
This discussion has been closed.