Implicit SQL Cursor Attributes

Oracle allows you to access information about the most recently executed implicit cursor by referencing the special implicit cursor attributes shown in Table 14-2. The table describes the significance of the values returned by these attributes for implicit SQL cursors. Because the cursors are implicit, they have no name and therefore the keyword "SQL" is used to denote the implicit cursor.

Table 14-2. Implicit SQL cursor attributes for queries
Name	Description
SQL%FOUND	Returns TRUE if one row (or more in the case of BULK COLLECT INTO) was fetched successfully.
SQL%NOTFOUND	Returns TRUE if a row was not fetched successfully (in which case Oracle will also raise the NO_DATA_FOUND exception).
SQL%ROWCOUNT	Returns the number of rows fetched from the specified cursor.
SQL%ISOPEN	Always returns FALSE for implicit cursors, because Oracle opens and closes implicit cursors atomically.

These cursor attributes return information about the execution of an INSERT, UPDATE, DELETE, or SELECT INTO statement. All the implicit cursor attributes return NULL if no implicit cursors have yet been executed in the session. Otherwise, the values of the attributes always refer to the most recently executed SQL statement, regardless of the block or program from which the SQL statement was executed.

Let's make sure we understand the implications of this last point. Consider the following two programs:

CREATE OR REPLACE PROCEDURE remove_from_circulation (isbn_in in book.isbn%TYPE)ISBEGIN DELETE FROM book WHERE isbn = isbn_in;END; CREATE OR REPLACE PROCEDURE show_book_countIS l_count INTEGER;BEGIN SELECT COUNT (*) INTO l_count FROM book; -- No such book! remove_from_circulation ('0-000-00000-0'); DBMS_OUTPUT.put_line (SQL%ROWCOUNT);END;

No matter how many rows of data are in the book table, we will always see "0" displayed in the output window. Because I call remove_from_circulation after my SELECT INTO statement, the SQL%ROWCOUNT reflects the outcome of my silly, impossible DELETE statement, and not the query.

If you want to make certain that you are checking the values for the right SQL statement, you should save attribute values to local variables immediately after execution of the SQL statement. I demonstrate this technique in the following example:

CREATE OR REPLACE PROCEDURE show_book_countIS l_count INTEGER; l_numfound PLS_INTEGER;BEGIN SELECT COUNT (*) INTO l_count FROM book; -- Take snapshot of attribute value: l_numfound := SQL%ROWCOUNT; -- No such book! remove_from_circulation ('0-000-00000-0'); -- Now I can go back to the previous attribute value. DBMS_OUTPUT.put_line (l_foundsome);END;

Now let's see how the cursor attributes work with implicit cursors.

SQL%FOUND

Use SQL%FOUND to determine if any rows were retrieved. SQL%FOUND will return TRUE if your SELECT INTO statement returned one or more rows; otherwise, it returns FALSE.

SQL%NOT FOUND

The SQL%NOT FOUND attribute acts in just the opposite fashion, returning TRUE if no rows were found (which, by the way, means that PL/SQL will have raised a NO_DATA_FOUND exception), and FALSE otherwise.

SQL%ISOPEN

The SQL%ISOPEN attribute is of no interest for SELECT INTOs (or any other implicit cursor). It always returns FALSE, because implicit cursors are opened and closed implicitly before you can reference SQL%ISOPEN to check their status.

SQL%ROWCOUNT

Use SQL%ROWCOUNT to determine the number of rows fetched by your SELECT INTO statement. This will generally return a value of 1 unless you are using BULK COLLECT INTO. If the value is 0, that means that no rows were found and once again PL/SQL will have raised a NO_DATA_FOUND exception.

SQL%ROWCOUNT returns 1 even when you select an aggregation of several rows into a variable. In fact: SELECT COUNT (*) INTO l_count FROM dual WHERE 1=2; will also set SQL%ROWCOUNT to 1 because 0 was successfully fetched into l_count.