%ROWCOUNT v17

The %ROWCOUNT attribute returns an integer showing the number of rows fetched so far from the specified cursor.

<cursor_name>%ROWCOUNT

cursor_name is the name of the cursor for which %ROWCOUNT returns the number of rows retrieved thus far. After the last row is retrieved, %ROWCOUNT remains set to the total number of rows returned until the cursor is closed. At that point, %ROWCOUNT throws an INVALID_CURSOR exception if referenced.

Referencing %ROWCOUNT on a cursor before it's opened or after it's closed results in an INVALID_CURSOR exception.

%ROWCOUNT returns 0 if it's referenced when the cursor is open but before the first FETCH. %ROWCOUNT also returns 0 after the first FETCH when the result set has no rows to begin with.

This example uses %ROWCOUNT:

CREATE OR REPLACE PROCEDURE cursor_example
IS
    v_emp_rec       emp%ROWTYPE;
    CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
    OPEN emp_cur_1;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_cur_1 INTO v_emp_rec;
        EXIT WHEN emp_cur_1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || '     ' || v_emp_rec.ename);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('**********************');
    DBMS_OUTPUT.PUT_LINE(emp_cur_1%ROWCOUNT || ' rows were retrieved');
    CLOSE emp_cur_1;
END;

This procedure prints the total number of rows retrieved at the end of the employee list as follows:

EXEC cursor_example;

EMPNO    ENAME
-----    -------
7369     SMITH
7499     ALLEN
7521     WARD
7566     JONES
7654     MARTIN
7698     BLAKE
7782     CLARK
7788     SCOTT
7839     KING
7844     TURNER
7876     ADAMS
7900     JAMES
7902     FORD
7934     MILLER
**********************
14 rows were retrieved