Oracle implicitly opens a cursor to process each SQL statement not associated with an explicit cursor. In PL/SQL, you can refer to the most recent implicit cursor as the SQL
cursor, which always has the attributes %FOUND
, %ISOPEN
, %NOTFOUND
, and %ROWCOUNT
. They provide information about the execution of data manipulation statements. The SQL
cursor has additional attributes, %BULK_ROWCOUNT
and %BULK_EXCEPTIONS
, designed for use with the FORALL
statement. For more information, see "Querying Data with PL/SQL".
sql cursor ::=
Keyword and Parameter Description
%BULK_ROWCOUNT
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 UPDATE
or DELETE
statement. If the ith execution affects no rows, %BULK_ROWCOUNT(i)
returns zero.
%BULK_EXCEPTIONS
An associative array that stores information about any exceptions encountered by a FORALL
statement that uses the SAVE 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 SQL%BULK_EXCEPTIONS.COUNT
, SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
specifies which iteration of the FORALL
loop caused an exception. SQL%BULK_EXCEPTIONS(i).ERROR_CODE
specifies the Oracle error code that corresponds to the exception.
%FOUND
Returns TRUE
if an INSERT
, UPDATE
, or DELETE
statement affected one or more rows or a SELECT
INTO
statement returned one or more rows. Otherwise, it returns FALSE
.
%ISOPEN
Always returns FALSE
, because Oracle closes the SQL
cursor automatically after executing its associated SQL statement.
%NOTFOUND
The logical opposite of %FOUND
. It returns TRUE
if an INSERT
, UPDATE
, or DELETE
statement affected no rows, or a SELECT
INTO
statement returned no rows. Otherwise, it returns FALSE
.
%ROWCOUNT
Returns the number of rows affected by an INSERT
, UPDATE
, or DELETE
statement, or returned by a SELECT
INTO
statement.
SQL
The name of the Oracle implicit cursor.
You can use cursor attributes in procedural statements but not in SQL statements. Before Oracle 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.
If a SELECT
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 SELECT
INTO
statement that calls a SQL aggregate function never raises NO_DATA_FOUND
, because those functions always return a value or a NULL
. In such cases, SQL%NOTFOUND
returns FALSE
. %BULK_ROWCOUNT
is not maintained for bulk inserts because that would be redundant as a typical insert affects only one row. See "Counting Rows Affected by FORALL with the %BULK_ROWCOUNT Attribute".
You can use the scalar attributes %FOUND
, %NOTFOUND
, and %ROWCOUNT
with bulk binds. For example, %ROWCOUNT
returns the total number of rows processed by all executions of the SQL statement. Although %FOUND
and %NOTFOUND
refer only to the last execution of the SQL statement, you can use %BULK_ROWCOUNT
to infer their values for individual executions. For example, when %BULK_ROWCOUNT(i)
is zero, %FOUND
and %NOTFOUND
are FALSE
and TRUE
, respectively.
For examples, see the following: