CLOSE Statement

The CLOSE statement allows resources held by an open cursor or cursor variable to be reused. No more rows can be fetched from a closed cursor or cursor variable. For more information, see "Managing Cursors".

Syntax

Text description of the illustration close_statement.gif

Keyword and Parameter Description

cursor_name

This identifies an explicit cursor previously declared within the current scope and currently open.

cursor_variable_name

This identifies a PL/SQL cursor variable (or parameter) previously declared within the current scope and currently open.

host_cursor_variable_name

This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.

Usage Notes

Once a cursor or cursor variable is closed, you can reopen it using the OPEN or OPEN-FOR statement, respectively. If you reopen a cursor without closing it first, PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN. However, you need not close a cursor variable before reopening it.

If you try to close an already-closed or never-opened cursor or cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.

Example

In the following example, after the last row is fetched and processed, you close the cursor variable emp_cv:

LOOP
   FETCH emp_cv INTO emp_rec;
   EXIT WHEN emp_cv%NOTFOUND;
   ...  -- process data record
END LOOP;
/* Close cursor variable. */
CLOSE emp_cv;

Related Topics

FETCH Statement, OPEN Statement, OPEN-FOR Statement