title: Cursor attribute %NOTFOUND not working in FOR loop
body: <p>I have a function that seems like it should return the correct data:</p>
  
  <pre><code>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 &lt;&gt; 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 &gt; 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;
  </code></pre>
  
  <p>However, instead of returning 'U' when no record is found, the function returns NULL.</p>
  
  <p>I have a working version of the function that uses:</p>
  
  <pre><code>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 &lt;&gt; 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 &gt; 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;
  </code></pre>
  
  <p>And another working version (although this one seems slower than the one above) that uses:</p>
  
  <pre><code>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 &lt;&gt; 2 or ethn is null) then
      if r_count = 0 then
        race := 'U';
      elsif r_count &gt; 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;
  </code></pre>
  
  <p>Can someone explain why %NOTFOUND isn't working as I would expect inside the FOR loop? I am using Oracle 11g.</p>