Open cursors…

Today I came across a very strange problem in the code I am working on. The code raised a correct error, when executed the first time, but ran with no problems the second time. After a lot of debugging (even built a log package, hopefully so generic it will one day make it to my toolbox) I found out that the problem was a cursor that wasn’t closed properly when the error appeared.

IF NOT ggp_cur%ISOPEN THEN OPEN ggp_cur( lv_ggp_opt_seqnr ); END IF;

Of course, I know, there should always be an error handling section in every part of the code, but this is some really old and sometimes fuzzy code, that doesn’t have these sections almost anywhere.

So, the simplest solution was to make sure the cursor was closed before we checked and opened the cursor.

IF ggp_cur%ISOPEN THEN CLOSE ggp_cur; END IF; --ggp_cur%ISOPEN IF NOT ggp_cur%ISOPEN THEN OPEN ggp_cur( lv_ggp_opt_seqnr ); END IF;

This code could be made even simpler by removing the second check, but that’s something for another day. At this moment the code does what it is supposed to do and I don’t have the time or get the resources to rebuild this code, although I would like to do that.

2 thoughts on “Open cursors…

  1. What kind of exception did you get?
    It seems like the cursor is closed when the procedure is completed (executed):
    SQL> create or replace procedure curtest
    2 is
    3 cursor c
    4 is
    5 select *
    6 from emp
    7 order by empno
    8 ;
    9 r c%rowtype;
    10 begin
    11 open c;
    12 fetch c into r;
    13 dbms_output.put_line (‘This is ‘||r.ename);
    14 end curtest;
    15 /

    Procedure created.

    SQL> exec curtest
    This is SMITH

    PL/SQL procedure successfully completed.

    SQL> exec curtest
    This is SMITH

    PL/SQL procedure successfully completed.

    SQL>
    ….

    Or are you using a cursor which is in a package specification?

    SQL> drop procedure curtest
    2 /

    Procedure dropped.

    SQL>
    SQL> create package curtest
    2 is
    3 cursor c
    4 is
    5 select *
    6 from emp
    7 order by empno;
    8 r c%rowtype;
    9 end;
    10 /

    Package created.
    SQL> create or replace procedure ctest
    2 is
    3 begin
    4 open curtest.c;
    5 fetch curtest.c into curtest.r;
    6 dbms_output.put_line (‘This is : ‘||curtest.r.ename);
    7 end ctest;
    8 /

    Procedure created.

    SQL> exec ctest
    This is : SMITH

    PL/SQL procedure successfully completed.

    SQL> exec ctest
    BEGIN ctest; END;

    *
    ERROR at line 1:
    ORA-06511: PL/SQL: cursor already open
    ORA-06512: at “ALEX.CURTEST”, line 5
    ORA-06512: at “ALEX.CTEST”, line 4
    ORA-06512: at line 1

    SQL>


  2. Actually, there was no error. There was just nothing done in the procedure because the cursor raised the NOTFOUND flag. And the following code checked for this flag to see if it had to be executed or not. The cursor is in the package spec because of subtypes based on the rowtype and these are then used as parameter types.


Leave a Reply to Alex Nuijten Cancel reply

Your email address will not be published. Required fields are marked *