[Chapter 6] 6.8 Closing Cursors

6.8 Closing Cursors

Early on I was taught that I should always clean up after myself. This rule is particularly important as it applies to cursors:

When you are done with a cursor, close 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.

An open cursor uses a certain amount of memory; the exact amount depends on the active set for the cursor. It can, therefore, use up quite a lot of the Shared Global Area of the RDBMS. The cursor can also cause the database to issue row-level locks when the FOR UPDATE clause is used in the SELECT statement.

6.8.1 Maximum Number of Cursors

When your database instance is started, an initialization parameter called OPEN_CURSORS specifies the maximum number of open cursors that a single-user process can have at once. This parameter does not control a system-wide feature, but rather the maximum address/memory space used by each process. If you are sloppy and do not close your cursors, you and all other users might encounter the dreaded error message:

ORA-01000:   maximum open cursors exceeded

You would rather not deal with this situation. For one thing, you will need to comb through your code and check for opened cursors which have not been closed. Even more frightening, your database administrator might insist that you tune your application so as to reduce the number of cursors you are using -- real code changes! I say this in jest, but in fact 90% of all the tuning that can be done for an application has nothing to do with the database, and everything to do with the application. Are the SQL statements tuned? Are you closing all opened cursors? And so on.

When you close a cursor, you disable it. Because the cursor no longer has an active set associated with it, you cannot fetch records from the cursor. The memory for that cursor is released and the number of cursors marked as currently open in your session is decreased by one, pulling you away from the brink of error ORA-01000.

You should close a cursor only if it is currently open. You can be sure of a cursor's status by checking the %ISOPEN cursor attribute before you try to close the cursor:

IF company_cur%ISOPEN
THEN
   CLOSE company_cur;
END IF;

6.8.2 Closing Local Cursors

If you declare a cursor in a PL/SQL block (an anonymous block, procedure, or function), the cursor is only defined within (is "local to") that block. When execution of the block terminates, PL/SQL will automatically close any local cursors which were left open without raising an exception.

I recommend, however, that you still include CLOSE statements for any cursor you opened in your programs. Don't depend on the runtime engine to do your cleaning up for you.

In addition, if your cursor is defined in a package, then its scope is not limited to any particular PL/SQL block. If you open such a cursor, it will stay open until you CLOSE it explicitly or you disconnect your Oracle session.