Open cursor paranoia

Most PL/SQL developers will likely have witnessed this phenomenon several times during their career. But only in other people's code, of course :-). I'm talking about PL/SQL code where every program unit ends like this:

exception
when others then
  if c%isopen
  then
    close c;
  end if;
  raise;
end;

where lines 3 to 6 are repeated for every cursor in the block above.

Proponents of open cursor paranoia justify it as defensive programming. Even when you open-fetch-close a cursor, the fetch could raise an exception and the close statement would not execute, leaving the cursor open, they say. A next execution would raise the dreaded "ORA-06511: PL/SQL: cursor already open".

Let's examine this claim. First by defining about which type of cursor we're talking about, since the term "cursor" is an overloaded one. Here I am talking about explicit session cursors in PL/SQL. The ones you declare and manage yourself. Now suppose you have this package:

SQL> create package pkg
  2  as
  3    procedure p;
  4  end pkg;
  5  /

Package created.

SQL> create package body pkg
  2  as
  3    procedure p
  4    is
  5      cursor c is select dummy from dual;
  6    begin
  7      open c;
  8    end p;
  9  end pkg;
 10  /

Package body created.

What will the next piece of code do? Will it raise an ORA-06511: cursor already open?

SQL> begin
  2    pkg.p;
  3    pkg.p;
  4  end;
  5  /

No it doesn't:

PL/SQL procedure successfully completed.

An explicit cursor variable has a scope, just like every other variable. Its scope here is the procedure. When the procedure ends, the cursor variable is gone and you can safely do an "open c" again. Note that PL/SQL has a PL/SQL cursor cache which keeps the cursor silently open in the background, so that a reopen of this cursor will not cause a reparse, not even a soft parse:

SQL> select cursor_type
  2    from v$open_cursor
  3   where sql_text = 'SELECT DUMMY FROM DUAL'
  4  /

CURSOR_TYPE
----------------------------------------------------------------
PL/SQL CURSOR CACHED

1 row selected.

By the way, in 11g, Oracle has added 3 extra columns to the V$OPEN_CURSOR view. The new column CURSOR_TYPE is very informative as you can see above.

The cursor type is PL/SQL CURSOR CACHED, because the SESSION_CACHED_CURSORS parameter is on its default of 50 and the cursor was closed in the PL/SQL code. When you switch this parameter off by setting it to 0, the entry disappears from V$OPEN_CURSOR, and the cursor is closed in the background as well:

SQL> alter session set session_cached_cursors = 0
  2  /

Session altered.

SQL> begin
  2    pkg.p;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select cursor_type
  2    from v$open_cursor
  3   where sql_text = 'SELECT DUMMY FROM DUAL'
  4  /

no rows selected

SQL> alter session set session_cached_cursors = 50
  2  /

Session altered.

Is there really no way that closing open cursors in the exception handler is useful? Maybe it once made sense in an earlier version? If you know this was the case once, please leave a comment. And it could be useful when the scope of the cursor is bigger than just the procedure, for example when it's the declared globally in the package. In this case the package state holds the state of the cursor variable, so with this package:

SQL> create or replace package body pkg
  2  as
  3    cursor c is select dummy from dual
  4    ;
  5    procedure p
  6    is
  7    begin
  8      open c;
  9    end p;
 10  end pkg;
 11  /

Package body created.

, you do get the ORA-06511:

SQL> begin
  2    pkg.p;
  3    pkg.p;
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "RWIJK.PKG", line 3
ORA-06512: at "RWIJK.PKG", line 8
ORA-06512: at line 3

And the cursor_type attribute of v$open_cursor now says:

SQL> select cursor_type
  2    from v$open_cursor
  3   where sql_text = 'SELECT DUMMY FROM DUAL '
  4  /

CURSOR_TYPE
----------------------------------------------------------------
OPEN-PL/SQL

1 row selected.

But this raises the question why you would ever want to have a globally defined cursor. Recently I've seen more than one application with so called cursor packages. Packages containing only cursors in their specification, as helper packages for the other non cursor packages in the application. A truly bad idea, not only for not hiding the implementation details from the caller, but also because once the package is called, the package has a state and the cursor variables don't get closed automatically, making the application vulnerable for ORA-06511's. Although, I can imagine a cursor package in a situation where a mid-tier application wants to handle record sets without having to deal with the SQL. I don't have experience with this scenario myself though.

Odds are that when you see "if c%isopen then close c; end if;" constructs being used abundantly throughout the code, most of them can happily be removed.