I have a cursor that is used to get some preliminary information for some other processing. It is possible that the query backing the cursor may not return any rows, and in these rare cases, we want to raise a special exception (handled and logged elsewhere so processing is not compeltely halted) so that the user knows about what is most likely bad input. Here's what it looks like:
open c_getPrs(in_pnum);
loop
fetch c_getPrs
into r_rpmRecord;
if c_getPrs%NOTFOUND then
raise X_INVALID_PNUM;
end if;
exit when c_getPrs%rowcount > 1 /*or c_getPrs%NOTFOUND*/;
end loop;
close c_getPrs;
The problem is that the if-statement ALWAYS executes so the exception is always raised, even when a row is returned. I'm not sure why. If there's a better way to handle this kind of logic, I'm open to that too ;)
Your code always goes round the loop twice, and so fails if there are less than 2 rows returned by the cursor. You probably don't need the loop at all:
I would prefer to avoid the cursor altogether, and use "select into" instead:
This will raise TOO_MANY_ROWS if the select returns more than 1 row. If you don't want that to happen, i.e. more than 1 row is OK, you could just add "AND ROWNUM = 1" to the query.