Cursor Attributes in Oracle PL/SQL

You can manipulate cursors using the OPEN, FETCH, and CLOSE statements. When you need to get information about the current status of your cursor, or the result of the last fetch from a cursor, you will access cursor attributes.

While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use.

Attribute	Explanation
%ISOPEN	- Returns TRUE if the cursor is open, FALSE if the cursor is closed.
%FOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Returns NULL if cursor is open, but fetch has not been executed. - Returns TRUE if a successful fetch has been executed. - Returns FALSE if no row was returned.
%NOTFOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Return NULL if cursor is open, but fetch has not been executed. - Returns FALSE if a successful fetch has been executed. - Returns TRUE if no row was returned.
%ROWCOUNT	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Returns the number of rows fetched. - The ROWCOUNT attribute doesn't give the real row count until you have iterated through the entire cursor. In other words, you shouldn't rely on this attribute to tell you how many rows are in a cursor after it is opened.

Example:
DECLARE
   CURSOR emps
   IS
        SELECT *
          FROM employees
         WHERE ROWNUM < 6
      ORDER BY 1;
   emp   employees%ROWTYPE;
   row   NUMBER := 1;
BEGIN
   OPEN emps;
   FETCH emps INTO emp;
   LOOP
      IF emps%FOUND
      THEN
         DBMS_OUTPUT.put_line (
            'Looping over record ' || row || ' of ' || emps%ROWCOUNT);
         FETCH emps INTO emp;
         row := row + 1;
      ELSIF emps%NOTFOUND
      THEN
         EXIT;
      END IF;
   END LOOP;
   IF emps%ISOPEN
   THEN
      CLOSE emps;
   END IF;
END;
/