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.
Once a cursor is closed, you can reopen it. Any other operation on a closed cursor raises the predefined exceptionINVALID_CURSOR.
In the next lesson, you will learn how to build a cursor block.
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:
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.