A m Z o n e

The code that never was

The below simple code is supposed to hike salary of employees who are not President, Clerk or Manager. But strangely enough it doesn't work. What is wrong with the below code? (Hint: No bugs, I am talking about a mistake that is often committed)

      declare 
        l_job1 varchar2(9) := 'PRESIDENT'; 
        l_job2 varchar2(9) := 'CLERK'; 
        l_job3 varchar2(9) := 'MANAGER'; 
        l_job4 varchar2(9) :=  ''; 

        cursor c_emp is 
           select empno, ename, sal 
           from   emp 
           where  job not in (l_job1, l_job2, l_job3, l_job4) 
           for update of sal; 
      begin 
        for rec in c_emp loop 
           update emp 
           set    sal = sal * 1.1 
           where  current of c_emp; 
           dbms_output.put_line(rec.ename || ' salary hiked!'); 
        end loop; 
      end; 

l_job4 has a null value.

Having a NULL value in the NOT IN list will nullify the complete search. As NULL cannot be equal to any other value, or NULL itself, the NOT IN clause returns no search results. This scenario is very common in situations where the NOT IN clause is using variables defined earlier in the code or values passed from another routine. This is difficult to debug. Use NVL or NVL2 to supply a value before hand for NULL.

Press the Back button of you Browser to go to previous page
Home