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.