Packages containing only cursors in their specification, as helper packages for the other non cursor packages in the application. Recently I've seen more than one application with so called cursor packages. In this case the package state holds the state of the cursor variable, so with this package:Īnd the cursor_type attribute of v$open_cursor now says:ģ where sql_text = 'SELECT DUMMY FROM DUAL 'īut this raises the question why you would ever want to have a globally defined cursor. 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. 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. SQL> alter session set session_cached_cursors = 50 SQL> alter session set session_cached_cursors = 0
![open cursors oracle open cursors oracle](https://qiita-image-store.s3.amazonaws.com/0/145865/a21c47bb-d4c8-6991-5951-61c72507a67c.png)
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: 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. The new column CURSOR_TYPE is very informative as you can see above. 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:ģ where sql_text = 'SELECT DUMMY FROM DUAL'īy the way, in 11g, Oracle has added 3 extra columns to the V$OPEN_CURSOR view. When the procedure ends, the cursor variable is gone and you can safely do an "open c" again.
Open cursors oracle code#
What will the next piece of code do? Will it raise an ORA-06511: cursor already open?Īn explicit cursor variable has a scope, just like every other variable. The ones you declare and manage yourself. Here I am talking about explicit session cursors in PL/SQL.
![open cursors oracle open cursors oracle](https://www.sqlsplus.com/wp-content/uploads/2020/08/Oracle-OPEN-operator-300x160.png)
First by defining about which type of cursor we're talking about, since the term "cursor" is an overloaded one.
![open cursors oracle open cursors oracle](https://imgs.developpaper.com/imgs/201707091547548.png)
A next execution would raise the dreaded "ORA-06511: PL/SQL: cursor already open". 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. Proponents of open cursor paranoia justify it as defensive programming. Where lines 3 to 6 are repeated for every cursor in the block above. I'm talking about PL/SQL code where every program unit ends like this: But only in other people's code, of course :-). Most PL/SQL developers will likely have witnessed this phenomenon several times during their career.