cursors - %notfound is true even when row is returned

Question

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 ;)

Answer 1

Hi,

your problem lies with your exit condition: on the first pass c_getPrms%rowcount is 1, so you get another pass which raises the exception.

Since you want only one fetch I would suggest the following construct:

OPEN c_getPrms(l_input);

FETCH c_getPrms
   INTO r_prmRecord;

IF c_getPrms%NOTFOUND THEN
   RAISE X_INVALID_PNUM;
END IF;

CLOSE c_getPrms;

I don't like explicit cursor much, so I will also suggest this synthax:

BEGIN
   SELECT ... 
     INTO r_prmRecord 
     FROM ... 
    WHERE ... AND rownum = 1; -- your cursor query
EXCEPTION
   WHEN no_data_found THEN
      RAISE X_INVALID_PNUM;
END;

Answer 2

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:

open c_getPrms(in_pnum);

fetch c_getPrms
 into r_prmRecord;

if c_getPrms%NOTFOUND then
  raise X_INVALID_PNUM;
end if;

close c_getPrms;

I would prefer to avoid the cursor altogether, and use "select into" instead:

begin
   select ...
   into   r_prmRecord
   from   ...
   where  ...
exception
   when no_data_found then
      raise X_INVALID_PNUM;
end;

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.