start page | rating of books | rating of authors | reviews | copyrights

Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 6.7 Column Aliases in Cursors Chapter 6
Database Interaction and Cursors
Next: 6.9 Cursor Attributes
 

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.


Previous: 6.7 Column Aliases in Cursors Oracle PL/SQL Programming, 2nd Edition Next: 6.9 Cursor Attributes
6.7 Column Aliases in Cursors Book Index 6.9 Cursor Attributes

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference