NULL value in NOT IN list
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. Best viewed in medium text size. Please refresh this page (F5) to view the latest information.
This page was created on 16-dec-2001 and last updated on 16-dec-2001.
please forward all queries to amar_padhi@fastmail.fm