Named Cursor Attribute

Every named cursor (explicit cursor or cursor variable) has four attributes, each of which returns information about the execution of a SQL DML statement.

Topics:

Syntax
Semantics
Usage
Examples
Related Topics

Syntax

named_cursor_attribute ::=

Description of the illustration named_cursor_attribute.gif

named_cursor ::=

Description of the illustration named_cursor.gif

Semantics

named_cursor_attribute

%ISOPEN

named_cursor%ISOPEN has the value TRUE if the cursor is open, and FALSE if it is not open.

%FOUND

named_cursor%FOUND has one of these values:

If the cursor is not open, INVALID_CURSOR
If cursor is open but no fetch was attempted, NULL.
If the most recent fetch returned a row, TRUE.
If the most recent fetch did not return a row, FALSE.

%NOTFOUND

named_cursor%NOTFOUND has one of these values:

If cursor is not open, INVALID_CURSOR.
If cursor is open but no fetch was attempted, NULL.
If the most recent fetch returned a row, FALSE.
If the most recent fetch did not return a row, TRUE.

%ROWCOUNT

named_cursor%ROWCOUNT has one of these values:

If cursor is not open, INVALID_CURSOR.
If cursor is open, the number of rows fetched so far.

named_cursor

explicit_cursor_name

The name of an explicit cursor.

cursor_parameter_name

The name of a formal cursor parameter.

cursor_variable_name

The name of a cursor variable.

host_cursor_variable_name

The name of a cursor variable that was declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. The data type of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Do not put space between the colon (:) and host_cursor_variable_name.

Usage

You can use cursor attributes in procedural statements, but not in SQL statements.

When a named cursor is opened, the rows that satisfy the associated query are the result set. Rows are fetched from the result set one at a time.

Every named cursor has its own attributes. You can open multiple named cursors, and then use %FOUND or %NOTFOUND to tell which cursors have rows left to fetch, and %ROWCOUNT to tell how many rows each cursor has fetched so far.

Because named_cursor%NOTFOUND has the value NULL before the first fetch, if FETCH never runs successfully, the condition named_cursor%NOTFOUND is never TRUE. If you use this condition to exit a loop, the loop can never end. Instead, use this condition:

named_cursor%NOTFOUND OR (named_cursor%NOTFOUND IS NULL);

Examples