|Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)
|PDF · Mobi · ePub|
A SQL (implicit) cursor is opened by the database to process each SQL statement that is not associated with an explicit cursor. Every SQL (implicit) cursor has six attributes, each of which returns useful information about the execution of a data manipulation statement.
A composite attribute designed for use with the
FORALL statement. This attribute acts like an index-by table. Its ith element stores the number of rows processed by the ith execution of an
DELETE statement. If the ith execution affects no rows,
%BULK_ROWCOUNT(i) returns zero.
An associative array that stores information about any exceptions encountered by a
FORALL statement that uses the
EXCEPTIONS clause. You must loop through its elements to determine where the exceptions occurred and what they were. For each index value i between 1 and
ERROR_INDEX specifies which iteration of the
FORALL loop caused an exception.
ERROR_CODE specifies the Oracle Database error code that corresponds to the exception.
TRUE if an
DELETE statement affected one or more rows or a
INTO statement returned one or more rows. Otherwise, it returns
FALSE, because the database closes the
SQL cursor automatically after executing its associated SQL statement.
The logical opposite of
%FOUND. It returns
TRUE if an
DELETE statement affected no rows, or a
INTO statement returned no rows. Otherwise, it returns
Returns the number of rows affected by an
DELETE statement, or returned by a
The name of the implicit cursor.
You can use cursor attributes in procedural statements but not in SQL statements. Before the database opens the
SQL cursor automatically, the implicit cursor attributes return
NULL. The values of cursor attributes always refer to the most recently executed SQL statement, wherever that statement appears. It might be in a different scope. If you want to save an attribute value for later use, assign it to a variable immediately.
INTO statement fails to return a row, PL/SQL raises the predefined exception
NO_DATA_FOUND, whether you check
SQL%NOTFOUND on the next line or not. A
INTO statement that invokes a SQL aggregate function never raises
NO_DATA_FOUND, because those functions always return a value or a
NULL. In such cases,
%BULK_ROWCOUNT is not maintained for bulk inserts because a typical insert affects only one row. See Counting Rows Affected by FORALL (%BULK_ROWCOUNT Attribute).
You can use the scalar attributes
%ROWCOUNT with bulk binds. For example,
%ROWCOUNT returns the total number of rows processed by all executions of the SQL statement. Although
%NOTFOUND refer only to the last execution of the SQL statement, you can use
%BULK_ROWCOUNT to deduce their values for individual executions. For example, when
%BULK_ROWCOUNT(i) is zero,