Using Explicit Cursors  «Prev  Next»

Lesson 7 Close a Cursor
ObjectiveDescribe how to close a Cursor

Close Cursor in PL/SQL

After opening a cursor and fetching the records, you must close it. The memory assigned to a cursor is cleared when you close it.

Close a cursor

The following diagram explains the syntax for closing a cursor and the variables in the syntax.

Oracle Cursor Syntax
Name of the cursor

Close Cursor
Once a cursor is closed, you can reopen it. Any other operation on a closed cursor raises the predefined exception INVALID_CURSOR.
In the next lesson, you will learn how to build a cursor block.

CLOSE Statement

The CLOSE statement closes a cursor or cursor variable, thereby allowing its resources to be reused. After closing a cursor, you can reopen it with the OPEN statement. You must close a cursor before reopening it.
After closing a cursor variable, you can reopen it with the OPEN-FOR statement. You need not close a cursor variable before reopening it.

Close the Cursor

Always close your explicit cursors. The comparison that is most frequently used is to equate forgetting to close a cursor to intentionally introducing a memory leak into code.
Remember, the context area is memory (part of the PGA) used for the cursor. Until the cursor is closed, the memory is not released. Oracle does check for abandoned cursors when the last block is finished, and it does automatically close the cursors when the outermost block completes. Do not rely on this to close your cursors, however. To close a cursor, use the following syntax:
CLOSE cursor_name;

Cursor_name is the name of the opened cursor. If a CLOSE is used on a cursor that is not currently open, the following exception is raised:

ORA-01001: invalid cursor
You made reference to a cursor that did not exist. This usually happens when you try to FETCH from a cursor or CLOSE a cursor before that cursor is OPENed.