Oracle ORA-06592 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06592: CASE Not Found While Executing CASE Statement

ORA-06592 is a runtime error in Oracle PL/SQL that occurs when a CASE statement executes but none of the WHEN conditions match the input value, and no ELSE clause is defined to handle the unmatched scenario. Unlike a SQL CASE expression (which simply returns NULL when no match is found), a PL/SQL CASE statement raises this exception explicitly. Understanding this distinction is key to writing robust PL/SQL code.

Top 3 Causes

1. Missing ELSE Clause

The most common cause is omitting the ELSE clause in a PL/SQL CASE statement when a runtime value falls outside all defined WHEN conditions.

-- Problematic Code
DECLARE
  v_grade VARCHAR2(1) := 'D'; -- 'D' not covered
  v_result VARCHAR2(20);
BEGIN
  CASE v_grade
    WHEN 'A' THEN v_result := 'Excellent';
    WHEN 'B' THEN v_result := 'Good';
    WHEN 'C' THEN v_result := 'Average';
    -- No ELSE: ORA-06592 is raised for 'D'
  END CASE;
END;
/

-- Fixed Code: Add ELSE clause
DECLARE
  v_grade VARCHAR2(1) := 'D';
  v_result VARCHAR2(20);
BEGIN
  CASE v_grade
    WHEN 'A' THEN v_result := 'Excellent';
    WHEN 'B' THEN v_result := 'Good';
    WHEN 'C' THEN v_result := 'Average';
    ELSE v_result := 'Undefined Grade'; -- Safe fallback
  END CASE;
  DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

2. NULL Value Not Handled

In Oracle, NULL cannot be compared with =. Using WHEN NULL THEN inside a simple CASE statement will never match because Oracle evaluates it as value = NULL, which is always FALSE. This causes ORA-06592 when the input is NULL.

-- Problematic Code: WHEN NULL never matches
DECLARE
  v_input VARCHAR2(10) := NULL;
  v_result VARCHAR2(20);
BEGIN
  CASE v_input
    WHEN NULL THEN v_result := 'No Value'; -- Never executes!
    WHEN 'A'  THEN v_result := 'Letter A';
  END CASE;
END;
/

-- Fixed Code: Use Searched CASE with IS NULL
DECLARE
  v_input VARCHAR2(10) := NULL;
  v_result VARCHAR2(20);
BEGIN
  CASE
    WHEN v_input IS NULL THEN v_result := 'No Value'; -- Correct
    WHEN v_input = 'A'   THEN v_result := 'Letter A';
    ELSE v_result := 'Other';
  END CASE;
  DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

3. Data Type Mismatch in WHEN Conditions

Comparing values of different data types in a CASE statement can lead to implicit conversion failures or unexpected comparison results, causing all WHEN conditions to silently fail.

-- Problematic Code: Mixing NUMBER and VARCHAR2 literals
DECLARE
  v_code NUMBER := 1;
  v_result VARCHAR2(20);
BEGIN
  CASE v_code
    WHEN '1' THEN v_result := 'One';  -- Type mismatch risk
    WHEN '2' THEN v_result := 'Two';
    -- No ELSE: ORA-06592 if conversion fails
  END CASE;
END;
/

-- Fixed Code: Use matching data types + ELSE
DECLARE
  v_code NUMBER := 1;
  v_result VARCHAR2(20);
BEGIN
  CASE v_code
    WHEN 1 THEN v_result := 'One';   -- Numeric literal matches
    WHEN 2 THEN v_result := 'Two';
    ELSE v_result := 'Other';
  END CASE;
  DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

Quick Fix: Handle CASE_NOT_FOUND Exception

If you need an immediate safeguard in existing code, catch the predefined CASE_NOT_FOUND exception:

DECLARE
  v_status VARCHAR2(10) := 'SUSPENDED';
  v_result VARCHAR2(50);
BEGIN
  CASE v_status
    WHEN 'ACTIVE'   THEN v_result := 'Active';
    WHEN 'INACTIVE' THEN v_result := 'Inactive';
  END CASE;
  DBMS_OUTPUT.PUT_LINE(v_result);
EXCEPTION
  WHEN CASE_NOT_FOUND THEN
    -- Predefined PL/SQL exception for ORA-06592
    DBMS_OUTPUT.PUT_LINE('Unhandled status: ' || v_status);
END;
/

Prevention Tips

Always include an ELSE clause in every PL/SQL CASE statement. Enforce this as a mandatory coding standard in your team's code review checklist and use static analysis tools to detect violations automatically before deployment.
Write unit tests that cover edge cases, including undefined values, NULL, and empty strings for any logic that uses a CASE statement. Tools like utPLSQL make it straightforward to assert correct behavior across all boundary conditions and catch ORA-06592 before it hits production.

Related Errors

CASE_NOT_FOUND: The named PL/SQL predefined exception for ORA-06592
ORA-06502: VALUE_ERROR — may follow if a type mismatch occurs during value assignment after the CASE executes
ORA-01403: NO_DATA_FOUND — another common PL/SQL runtime exception often seen alongside CASE logic in data-driven procedures

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community