CLOSE Statement

The CLOSE statement indicates that you are finished fetching from a cursor or cursor variable, and that the resources held by the cursor can be reused.

Syntax

Description of the illustration close_statement.gif

Keyword and Parameter Description

cursor_name, cursor_variable_name, host_cursor_variable_name

When you close the cursor, you can specify an explicit cursor or a PL/SQL cursor variable, previously declared within the current scope and currently open.

You can also specify 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. You must close a cursor before opening it again, otherwise PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN. You do not need to close a cursor variable before opening it again.

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

Example

DECLARE
   CURSOR emp_cv IS SELECT * FROM employees WHERE first_name = 'John';
   emp_rec employees%ROWTYPE;
BEGIN
   OPEN emp_cv;
   LOOP
       FETCH emp_cv INTO emp_rec;
       EXIT WHEN emp_cv%NOTFOUND;
   END LOOP;
   CLOSE emp_cv; /* Close cursor variable after last row is processed. */
END;
/

Related Topics

FETCH Statement, OPEN Statement, OPEN-FOR Statement, "Querying Data with PL/SQL".