Closing Explicit Cursors

Early on I was taught to clean up after myself, and I tend to be a bit obsessive (albeit selectively) about this later in life. Cleaning up after oneself is an important rule to follow in programming and can be crucial when it comes to cursor management. So be sure to close a cursor when you are done with it!

Here is the syntax for a CLOSE cursor statement:

CLOSE cursor_name;

where cursor_name is the name of the cursor you are closing.

Here are some special considerations regarding the closing of explicit cursors:

· If you declare and open a cursor in a procedure, be sure to close it again. Otherwise, you've just programmed a memory leak—and that's not good! Strictly speaking, a cursor (like any other data structure) should be automatically closed and destroyed when it goes out of scope. In fact, in many cases PL/SQL does check for and implicitly close any open cursors at the end of a procedure call, function call, or anonymous block. However, the overhead involved in doing that is significant, so for the sake of efficiency there are cases where PL/SQL does not immediately check for and close any open cursors. In addition, REF CURSORs are, by design, never closed implicitly. The one thing you can count on is that whenever the outermost PL/SQL block ends and control is returned to SQL or some other calling program, PL/SQL will at that point implicitly close any cursors (but not REF CURSORS) left open by that block or nested blocks.

Nested anonymous blocks provide an example of one case (at least in Oracle9i Release 1) where PL/SQL does not implicitly close cursors. For an interesting discussion of this issue see Jonathan Gennick's article, "Does PL/SQL Implicitly Close Cursors?," at http://gennick.com/open_cursors.html.

 

 

· If you declare a cursor in a package at the package level and then open it in a particular block or program, that cursor will stay open until you explicitly close it or until your session closes. Therefore, it is extremely important that you include your CLOSE statement for any packaged cursors as soon as you are done with them (and in the exception section as well), as in the following:

· BEGIN· OPEN my_package.my_cursor;· · ... Do stuff with the cursor· · CLOSE my_package.my_cursor;· EXCEPTION· WHEN OTHERS· THEN· CLOSE my_package.my_cursor;END;

· If you have opened a SELECT FOR UPDATE query, it is especially important to close the cursor as soon as you are done, as this query causes row-level locks to be applied.

· You should close a cursor only if it is currently open. You can check a cursor's status with the %ISOPEN cursor attribute before you try to close the cursor:

· IF company_cur%ISOPEN· THEN· CLOSE company_cur;END IF;

· If you leave too many cursors open, you may exceed the value set by the database initialization parameter, OPEN_CURSORS. If this happens, you will encounter the dreaded error message:

ORA-01000: maximum open cursors exceeded

If you get this message, check your usage of package-based cursors to make sure they are closed when no longer needed.