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

Closing Cursor Diagram in PL/SQL

Syntax and example for closing a cursor
Syntax and example for closing a cursor
Name of the cursor
Name of the cursor

You can close an open cursor by executing the CLOSE_CURSOR procedure of the DBMS_SQL package. The example in the following listing, from the ANYSTRING procedure, shows that the
CLOSE_CURSOR procedure uses the cursor ID value as its sole input parameter:

Closing a cursor frees the memory used by the cursor and reduces the number of concurrently open cursors in your session. Unless you need very detailed control over the steps involved in executing dynamic SQL, you should use the EXECUTE IMMEDIATE approach.
create or replace procedure ANYSTRING(String IN VARCHAR2) AS
Cursor_Name INTEGER;
DBMS_SQL.PARSE(Cursor_Name, String, dbms_sql.Native);
Ret := DBMS_SQL.EXECUTE(Cursor_Name);

Once a cursor is closed, you can reopen it. Any other operation on a closed cursor raises the predefined exception INVALID_CURSOR.

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. In the next lesson, you will learn how to build a cursor block.

SEMrush Software