Cursor attribute %NOTFOUND not working in FOR loop

Question

I have a function that seems like it should return the correct data:

create or replace function baninst1.ab6_student_race(pidm number)
  return varchar2
is

  race varchar2(1);
  ethn varchar2(1);

  cursor c_student_race_codes is
    select prac1.gorprac_race_cde
    from general.gorprac prac1
    where prac1.gorprac_pidm = pidm;

begin

  select pers1.spbpers_ethn_cde
  into ethn
  from saturn.spbpers pers1
  where pers1.spbpers_pidm = pidm;

  if ethn = '2' then
    race := 'H';
  elsif (ethn <> 2 or ethn is null) then

    for r_student_race_codes in c_student_race_codes loop
      if c_student_race_codes%notfound then
        race := 'U';
      elsif c_student_race_codes%rowcount > 1 then
        race := 'M';
      else race := r_student_race_codes.gorprac_race_cde;
      end if;
    end loop;

  end if;

  return race;

  exception
    when others then
      dbms_output.put_line(sqlerrm);

end;

However, instead of returning 'U' when no record is found, the function returns NULL.

I have a working version of the function that uses:

create or replace function baninst1.ab6_student_race(pidm number)
  return varchar2
is

  race varchar2(1);
  r_count number(1);
  ethn varchar2(1);

  cursor c_student_race_codes is
    select prac1.gorprac_race_cde as race_code
          ,count(prac1.gorprac_race_cde) as race_count
    from general.gorprac prac1
    where prac1.gorprac_pidm = pidm
    group by prac1.gorprac_race_cde;

begin

  select pers1.spbpers_ethn_cde
  into ethn
  from saturn.spbpers pers1
  where pers1.spbpers_pidm = pidm;

  if ethn = '2' then
    race := 'H';
  elsif (ethn <> 2 or ethn is null) then
    open c_student_race_codes;
    fetch c_student_race_codes into race, r_count;
    if c_student_race_codes%notfound then
      race := 'U';
    elsif r_count > 1 then
      race := 'M';
    end if;
    close c_student_race_codes;
  end if;

  return race;

  exception
    when others then
      dbms_output.put_line(sqlerrm);

end;

And another working version (although this one seems slower than the one above) that uses:

create or replace function baninst1.ab6_student_race(pidm number)
  return varchar2
is

  race varchar2(1);
  r_count number(1);
  ethn varchar2(1);

begin

  select pers1.spbpers_ethn_cde
  into ethn
  from saturn.spbpers pers1
  where pers1.spbpers_pidm = pidm;

  select count(prac1.gorprac_race_cde)
  into r_count
  from general.gorprac prac1
  where prac1.gorprac_pidm = pidm;

  if ethn = '2' then
    race := 'H';
  elsif (ethn <> 2 or ethn is null) then
    if r_count = 0 then
      race := 'U';
    elsif r_count > 1 then
      race := 'M';
    else

      select prac2.gorprac_race_cde
      into race
      from general.gorprac prac2
      where prac2.gorprac_pidm = pidm;

    end if;
  end if;

  return race;

  exception
    when others then
      dbms_output.put_line(sqlerrm);

end;

Can someone explain why %NOTFOUND isn't working as I would expect inside the FOR loop? I am using Oracle 11g.

Answer 1

If no records are found the for loop never executes, so the check for %notfound is never executed. Credit to GriffeyDog.