Row counts in a cursor

Viewed 43

How to get the row counts in a cursor I have used cursor_name%rowcount but this returns always 0?

1 Answers

You will need to fetch it first. Example:

DECLARE
     CURSOR cursor_name IS
            SELECT * FROM table_name;
     tmp    cursor_name%ROWTYPE;
     v_cnt  NUMBER;
BEGIN
     OPEN cursor_name;
     LOOP
          FETCH cursor_name
          INTO tmp;
          EXIT WHEN cursor_name%NOTFOUND;
     END LOOP;
     v_cnt := cursor_name%ROWCOUNT;
     CLOSE cursor_name;
END;
/

Regarding to documentation:

%ROWCOUNT Attribute: A cursor attribute that can be appended to the name of a cursor or cursor variable. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT returns 0. Thereafter, it returns the number of rows fetched so far. The number is incremented if the latest fetch returned a row.

Related