Count the number of records in a oracle cursor

Question

I need to know the number of records of a oracle cursor (an output parameter of a procedure).

The cursor is declared on the body of the Oracle Package and is a type IS REF CURSOR;

Inside the procedure 2 (same package), I call the procedure 1 and get the output cursor.

I need to loop this output cursor and count the quantity of records. I've tested a lot of examples but, any example works.

Thanks,

Answer 1

It is not possible to get count of cursor without iterating over it. Cursor is like pointer to data not data itself. So if you need to count you have to do loop until cursor%notfound. There is something like cursor%ROWCOUNT but documentation says:

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.

So only way to get a count is either execute select count(*) from --the same condition as in cursor or loop over cursor (fetch all data) and then check %rowcount or just increment variable for every cursor record however probably the fastest way is bulk fetch all records from cursor and use %rowcount.