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.
I hardly ever used isopen, but it's nice to read this elaboration on this issue.
ReplyDeleteCursor packages are handy to overcome the nusance of maintaining similar clauses. You could also make nice APIs which return complete datasets, but this requires more coding to use.
What you say is true of cursors, but not REF CURSORs. If your REF CURSOR is *output* you obviously should not close it, but Table functions often use a REF CURSOR as *input*, which means it needs to be closed after use.
ReplyDelete@Stew: It's not about whether the cursor is a ref cursor or not, it's about the cursor scope. If you use ref cursors as an input for table functions, the cursor definition is obviously defined at the package level, else the SQL engine cannot see it. This also means that the caller should manage the cursor.
ReplyDeleteUsing table functions with a cursor variable as input is somewhat similar to my "where a mid-tier application wants to handle record sets without having to deal with the SQL" and is indeed a valid exception to the rule.
According to this post in the OTN forum, ref cursors have session scope so they have to be closed explicitly by the last program to use them.
ReplyDeletehttp://forums.oracle.com/forums/message.jspa?messageID=2390438#2390438
Isn't that the whole idea, to have one cursor referred to by different programs? How could a garbage collector possibly know when to close it?
Hi Stew,
ReplyDeleteJust test it and you'll see that when you declare a local variable as a ref cursor, open the ref cursor without closing it, and that scope ends at the end of the procedure, no cursors are left open. When you pass a ref cursor handle to some client application (it isn't possible to declare a global ref cursor variable, you'll get a PLS-00994), that client application becomes responsible for opening and closing the cursor. This doens't contradict with what Billy says in that link, by the way. He only misses (or I misunderstood him) the fact that you can have a local ref cursor variable that isn't shared with a client application. What he describes is a much more typical use of ref cursors, though: for a client application.
Regards,
Rob.
Interesting - I have seen that scattered throughout our code here. One thing I'm wondering though - in some of our packages some cursors are declared at the start of the package body; if pkg.A calls pkg.B and B opens the cursor, will the cursor remain open if A calls B again in the same call of A?
ReplyDelete