How to get the row counts in a cursor I have used cursor_name%rowcount but this returns always 0?
How to get the row counts in a cursor I have used cursor_name%rowcount but this returns always 0?
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.