The curse of the cursor

For some reason, many Oracle developers avoid SELECT INTO as if it were dangerous, preferring to "have more control" over their code by using cursors for everything. This morning I spent over an hour debugging some code written by one such developer, only to find that the issue I was trying to fix was one that would have been caught by the original developer immediately had he used SELECT INTO.

The code resembled this:

procedure do_something (p_empno in number)
is
   l_empno number;
   l_boss_ind varchar2(1) := 'N';
   cursor c_emp (cp_empno number) is
      select job
      from emp
      where empno = cp_empno;
begin
   for rec in c_emp (l_empno) loop
      if rec.job = 'MANAGER' then
         l_boss_ind = 'Y';
      end if;
   end loop;
   insert into some_table (empno, boss_ind) values (p_empno, l_boss_ind);
end;

Sometimes the BOSS_IND column wasn't being set correctly. The code was a lot more complex than this in reality. You can probably easily and quickly spot the error above, but in the real procedure there was a lot of other code that could also set the BOSS_IND under different circumstances, and it was, as I said, about an hour before I spotted the problem which is: variable l_empno was not set when the cursor was opened (it got set further down), so the cursor returned no rows and the code inside the loop was never executed.

If the developer had coded this using SELECT INTO, a very unexpected NO_DATA_FOUND exception would have been raised as soon as he ran this for the first time, and the bug would never have reached system testing (as it had). Assuming of course he didn't decide that SELECT INTO had "caused" the bug, and didn't "fix" it by changing to use a cursor...