PL/SQL Cursor Handling Explained

 

If you've somehow jumped directly to this page, perhaps from a search engine, you should read the following two articles as background for understanding this one:
Does PL/SQL Implicitly Close Cursors?
More on PL/SQL's Cursor Handling
You might also wish to read On the Importance of Mental Models.


 

On 15-Feb-2002, Steven Feuerstein, Bill Pribyl, and myself participated in a conference call on the issue of PL/SQL cursor handling with Bryn Llewellyn, who is Oracle's PL/SQL Product Manager. Our conversation with Bryn was very enlightening, and I want to write more someday about what he told us, but what's important now is for me to share what Bryn told me us about when and whether PL/SQL implicitly closes cursors on our behalf:

  • We really should close our own cursors. To not close a cursor is to explicitly program a memory leak

  • Notwithstanding the above, for pragmatic considerations (i.e. to prevent us from crashing the server through our own carelessness), PL/SQL will close cursors on our behalf when we open them and then let them go out of scope. This is undocumented behavior, should not be relied upon, and may not hold true for releases of Oracle earlier than Oracle9i.

  • There are exceptions to the rule that PL/SQL will close cursors implicitly. One exception applies to the very first test I did in my first article in this series: PL/SQL does not implicitly close cursors at the end of a nested anonymous block. However, when the outer block ends, any leftover cursors from the inner block will be detected and closed. This explains all the behavior I described in my first article. This is a tradeoff made to keep PL/SQL efficient. Again, exceptions are undocumented, may vary from one release to the next, and should not be relied upon.

  • REF CURSORs are never closed implicitly, because one of the design goals for REF CURSOR variables was to allow them to be passed from one PL/SQL block to another. Or from within a PL/SQL block to an external program such as SQL*Plus. This explains the behavior described in my second article in this series.

Remember, close those cursors!