1

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.

  • 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 Sep 27 '17 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 Sep 27 '17 at 21:53
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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