Close Cursor in PL/SQL

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

cursor_name
Name of the cursor
pets
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:

DBMS_SQL.CLOSE_CURSOR(Cursor_Name);

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;
Ret INTEGER;
BEGIN
Cursor_Name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(Cursor_Name, String, dbms_sql.Native);
Ret := DBMS_SQL.EXECUTE(Cursor_Name);
DBMS_SQL.CLOSE_CURSOR(Cursor_Name);
END;

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

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.

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.