How to find number of rows in cursor

Question

I would like to find the number of rows in a cursor. Is there a keyword that can help? Using COUNT, we have to write a query. Any help will be greatly appreciated.

Answer 1

You can also use BULK COLLECT so that a LOOP is not needed,

DECLARE
    CURSOR c 
    IS   SELECT *
           FROM employee;
    TYPE emp_tab IS TABLE OF c%ROWTYPE INDEX BY BINARY_INTEGER;
    v_emp_tab emp_tab;
BEGIN
    OPEN c;
    FETCH c BULK COLLECT INTO v_emp_tab;
    DBMS_OUTPUT.PUT_LINE(v_emp_tab.COUNT);
    CLOSE c;
END;
/

Edit: changed employee%ROWTYPE to c%ROWTYPE

Answer 2

The cursor_variable.%ROWCOUNT is the solution. But its value will be 0 if you check it after opening. You need to loop through all the records, to get the total row count. Example below:

DECLARE 
  cur sys_refcursor;
  cur_rec YOUR_TABLE%rowtype;
BEGIN
  OPEN cur FOR
  SELECT * FROM YOUR_TABLE;

  dbms_output.put_line(cur%rowcount);--returning 0

  LOOP
    FETCH cur INTO cur_rec;  
    EXIT WHEN cur%notfound;
    dbms_output.put_line(cur%rowcount);--will return row number beginning with 1
    dbms_output.put_line(cur_rec.SOME_COLUMN);
  END LOOP;

  dbms_output.put_line('Total Rows: ' || cur%rowcount);--here you will get total row count
END;
/