Using Explicit Cursors  «Prev 

Implicit cursor Guidelines

PL/SQL example

The implicit cursor attributes are:
  1. SQL%ISOPEN Attribute: Is the Cursor Open?
  2. SQL%FOUND Attribute: Were Any Rows Affected?
  3. SQL%NOTFOUND Attribute: Were No Rows Affected?
  4. SQL%ROWCOUNT Attribute: How Many Rows Were Affected?
  5. SQL%BULK_ROWCOUNT
  6. SQL%BULK_EXCEPTIONS

Getting Number of Rows Affected by FORALL Statement

After a FORALL statement completes, you can get the number of rows that each DML statement affected from the implicit cursor attribute SQL%BULK_ROWCOUNT.
To get the total number of rows affected by the FORALL statement, use the implicit cursor attribute SQL%ROWCOUNT. described in described next below this section.
SQL%BULK_ROWCOUNT is like an associative array whose ith element is the number of rows affected by the ith DML statement in the most recently completed FORALL statement. The data type of the element is PLS_INTEGER.

SQL%ROWCOUNT Attribute: How Many Rows Were Affected?

SQL%ROWCOUNT returns:
  1. NULL if no SELECT or DML statement has run
  2. Otherwise, the number of rows returned by a SELECT statement or affected by a DML statement (a PLS_INTEGER)
Note: If the number of rows exceeds the maximum value for a PLS_INTEGER, then the element has a negative value.

Cursor attributes for FORALL

You can use cursor attributes after you execute a FORALL statement to get information about the DML operation run within FORALL. Oracle also offers an additional attribute, %BULK_ROWCOUNT, to give you more granular information about the results of the bulk DML statement.

Table 6-3 describes the significance of the values returned by these attributes for FORALL.
Implicit SQL cursor attributes for FORALL statements
Table 6-3. Implicit SQL cursor attributes for FORALL statements

Implicit Cursor Guidelines

A SELECT statement must return exactly one record
  1. A SELECT statement must return exactly one record.
  2. If the result set includes no record or more than one record, PL/SQL returns an error and passes, control to the exception section of the PL/SQL block.
  3. In an implicit cursor with an INSERT, UPDATE, or DELETE statement, the result set can be more than one record.

Implicit Cursor Example

DECLARE pet_count NUMBER 
DECLARE
pet_count NUMBER;
BEGIN
  SELECT CURRENT_INVENTORY_COUNT
    INTO pet_count
  FROM PRODUCT
    WHERE PRODUCT_NAME = 'Canary';			 
	DBMS_OUTPUT.PUT_LINE('Number of Canary's = ' || pet_count);
END;