Oracle: Get the row count in a cursor when using a OPEN-FOR-USING statement

Question

Within an Oracle procedure, after opening a cursor for a select statement, I fail to find a mean to count the number of rows fetched.

OPEN mycursor FOR
SELECT * FROM TABLE;
-- mycursor%ROWCOUNT is always set to 0, even if the cursor has rows.
IF mycursor%ROWCOUNT = 0
THEN
 <error processing code here>
END IF;

This is expected, as documented at Oracles's documentation website:

The OPEN-FOR-USING statement associates a cursor variable with a query, executes the query, identifies the result set, positions the cursor before the first row in the result set, then zeroes the rows-processed count kept by %ROWCOUNT.

So, except for running a second, redundant 'select count(*) from table', are there any other means to find out the number of rows within the cursor?

EDIT #1: I am not looping after loading the cursor; it is returned as is to the calling procedure. However, I must raise an exception if there are no row. These are the specifications.

• a1ex07: You don't need redundant SELECT COUNT, you may just add count (analytic version) to your main query : SELECT .... , COUNT(1) OVER (PARTITION BY NULL) as rec_count .....
• EdStevens: Just assume the cursor has a rowcount > 0 and start to process it, with an exception handler for 'NO_DATA_FOUND'. See oracle.com/technetwork/issue-archive/2013/13-mar/…

Answer 1

Do the ROWCOUNT check after looping through the set. If ROWCOUNT will remain 0 if there were no data in the cursor. You could use a FOR LOOP construct like:

OPEN ....
FOR ... IN mycursor
LOOP
 /* Process data here */
END LOOP:
IF mycursor%ROWCOUNT = 0 ...
CLOSE mycursor;