ORA-06592: CASE_NOT_FOUND Exception

CASE is a smarter rewrite for IF-THEN-ELSE, we said. It is for sure, but there is a difference in the way the ELSE part of it is handled.

Compare the code units below, one using CASE WHEN the other using IF-ELSIF. Both are identical in logic – two defined conditions, no ELSE path.

SQL> -- Procedure with CASE, without ELSE
SQL> create or replace procedure test_case_excp (i number)
  2  as
  3    output varchar2(50);
  4  begin
  5    case i
  6      when 1 then output := 'One';
  7      when 2 then output := 'Two';
  8    end case;
  9
 10    dbms_output.put_line(output);
 11
 12  end;
 13  /

Procedure created.

SQL> -- Procedure with IF, without ELSE
SQL> create or replace procedure test_if_excp (i number)
  2  as
  3    output varchar2(50);
  4  begin
  5    if (i = 1) then
  6      output := 'One';
  7    elsif (i = 2) then
  8      output := 'Two';
  9    end if;
 10
 11    dbms_output.put_line(output);
 12  end;
 13  /

Procedure created.

Notice the difference in the executions for the two.

With IF, the procedure completes successfully whether a defined leg or an undefined leg is chosen. If none of the defined legs is chosen, no action is taken.

SQL> -- Testing a defined condition
SQL> exec test_if_excp (1);
One

PL/SQL procedure successfully completed.

SQL> -- Testing an undefined condition
SQL> exec test_if_excp (3);

PL/SQL procedure successfully completed.

With CASE, the procedure completes successfully only if a defined leg is chosen. If an undefined leg is chosen, the CASE_NOT_FOUND exception (ORA-06592: CASE not found while executing CASE statement)) is raised.

SQL> -- Testing a defined condition
SQL> exec test_case_excp (1);
One

PL/SQL procedure successfully completed.

SQL> -- Testing an undefined condition
SQL> exec test_case_excp (3);
BEGIN test_case_excp (3); END;

*
ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at "SCOTT.TEST_CASE_EXCP", line 5
ORA-06512: at line 1

The situation can be remedied by defining an ELSE path for the CASE statement.

The execution now is exception-free:

SQL> -- Procedure with CASE, with ELSE
SQL> create or replace procedure test_case_excp (i number)
  2  as
  3    output varchar2(50);
  4  begin
  5    case i
  6      when 1 then output := 'One';
  7      when 2 then output := 'Two';
  8      else output := 'Not one or two';
  9    end case;
 10
 11    dbms_output.put_line(output);
 12
 13  end;
 14  /

Procedure created.

SQL> -- Testing a defined condition
SQL> exec test_case_excp (1);
One

PL/SQL procedure successfully completed.

SQL> -- Testing an undefined condition
SQL> exec test_case_excp (3);
Not one or two

PL/SQL procedure successfully completed.