ORACLE CASE_NOT_FOUND EXCEPTION HANDLING.

ORACLE CASE_NOT_FOUND  EXCEPTION HANDLING.

When Used Case Statement in PL/SQL block, it should have ELSE block/Handle all options your returns. otherwise CASE_NOT_FOUND exception will be displayed.

declare
*
ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 9

Ex: Handling Case_not_Found exception in PL/SQL block

declare
cursor c1 is select * from emp;
e emp%rowtype;
begin
open c1;
loop
   fetch c1 into e;
   exit when c1%notfound;
  

 case e.job 
when  'CLERK'  then  dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);
when 'SALESMAN' then dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);
end case;

end loop;

OUTPUT:

 will display 

ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 9

Handling Exception with Exception statement

declare
cursor c1 is select * from emp;
e emp%rowtype;
begin
open c1;
loop
   fetch c1 into e;
   exit when c1%notfound;
   case e.job 
when  'CLERK'  then  dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);
when 'SALESMAN' then dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);

end case;
--  dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);
end loop;
exception
when CASE_NOT_FOUND then
         dbms_output.put_line('case not handled,u must handle all options/else case required.');
end;

Handling CASE_NOT_FOUND Exception with ELSE statement

when  'CLERK'  then  dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);
when 'SALESMAN' then dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);
else dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);

Tags: ORACLE CASE_NOT_FOUND  EXCEPTION HANDLING,Handling CASE_NOT_FOUND Exception with ELSE statement,

Handling CASE_NOT_FOUND Exception with Exception statement, PL/SQL Exception handling,
ORA-06592: CASE not found while executing CASE statement