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

-- mycursor%ROWCOUNT is always set to 0, even if the cursor has rows.
IF mycursor%ROWCOUNT = 0
  <error processing code here>

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.

  • 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 .....
    – a1ex07
    Commented Sep 27, 2017 at 20:03
  • 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/…
    – EdStevens
    Commented Sep 27, 2017 at 21:53

1 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
    /* Process data here */

IF mycursor%ROWCOUNT = 0 ...

CLOSE mycursor;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.