Explicit Cursor Attributes

Oracle offers four attributes (%FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT) that allow you to retrieve information about the state of your cursor. Reference these attributes using this syntax:

cursor%attribute

where cursor is the name of the cursor you have declared.

Table 14-3 describes the significance of the values returned by these attributes for explicit cursors.

Table 14-3. Values returned by cursor attributes
Name	Description
cursor%FOUND	Returns TRUE if a record was fetched successfully.
cursor%NOTFOUND	Returns TRUE if a record was not fetched successfully.
cursor%ROWCOUNT	Returns the number of records fetched from the specified cursor at that point in time.
cursor%ISOPEN	Returns TRUE if the specified cursor is open.

Table 14-4 shows you the attribute values you can expect to see both before and after the specified cursor operations.

Table 14-4. Cursor attribute values
	%FOUND	%NOTFOUND	%ISOPEN	%ROWCOUNT
Before OPEN	ORA-01001 raised	ORA-01001 raised	FALSE	ORA-01001 raised
After OPEN	NULL	NULL	TRUE
Before first FETCH	NULL	NULL	TRUE
After first FETCH	TRUE	FALSE	TRUE
Before subsequent FETCH(es)	TRUE	FALSE	TRUE
Before subsequent FETCH(es)	TRUE	FALSE	TRUE	Data dependent
Before last FETCH	TRUE	FALSE	TRUE	Data dependent
After last FETCH	FALSE	TRUE	TRUE	Data dependent
Before CLOSE	FALSE	TRUE	TRUE	Data dependent
After CLOSE	Exception	Exception	FALSE	Exception

Here are some things to keep in mind as you work with cursor attributes for explicit cursors:

· If you try to use %FOUND, %NOTFOUND, or %ROWCOUNT before the cursor is opened or after it is closed, Oracle will raise anINVALID_CURSOR error (ORA-01001).

· If the result set was empty after the very firstFETCH, then attributes will return values as follows: %FOUND = FALSE, %NOTFOUND = TRUE, and %ROWCOUNT = 0.

· If you are using BULK COLLECT, it is possible for%ROWCOUNT to return a value other than or 1; it will, in fact, return the number of rows fetched into the associated collections. For more details, see Section 14.4.

The following code showcases many of these attributes:

CREATE OR REPLACE PACKAGE bookinfo_pkgIS CURSOR bard_cur IS SELECT title, date_published FROM books WHERE UPPER(author) LIKE 'SHAKESPEARE%';END bookinfo_pkg; DECLARE bard_rec bookinfo_pkg.bard_cur%ROWTYPE;BEGIN -- Check to see if the cursor is already opened. -- This is possible, since it is a packaged cursor. -- If so, first close it and then re-open it to -- ensure a "fresh" result set. IF bookinfo_pkg.bard_cur%ISOPEN THEN CLOSE bookinfo_pkg.bard_cur; END IF; OPEN bookinfo_pkg.bard_cur; -- Fetch each row, but stop when I've displayed the -- first five works by Shakespeare or when I have -- run out of rows. LOOP FETCH bookinfo_pkg.bard_cur INTO bard_rec; EXIT WHEN bookinfo_pkg.bard_cur%NOTFOUND OR bookinfo_pkg.bard_cur%ROWCOUNT = 6; DBMS_OUTPUT.put_line ( bcur%ROWCOUNT || ') ' || rec.title || ', published in ' || TO_CHAR (rec.date_published, 'YYYY') ); END LOOP; CLOSE bard_cur;END;