6.8 Closing Cursors

Chapter 6
Database Interaction and 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;