Handling CASE_NOT_FOUND exception in PL/SQL with example code

PL/SQL Exception Handling: Exercise-11 with Solution

Handle the CASE_NOT_FOUND exception when no branch matches the value of a CASE statement.

Sample Solution:

PL/SQL Code:

DECLARE
v_value NUMBER := 5;
BEGIN
  CASE v_value
    WHEN 1 THEN
      DBMS_OUTPUT.PUT_LINE('Branch 1');
    WHEN 2 THEN
      DBMS_OUTPUT.PUT_LINE('Branch 2');
    WHEN 3 THEN
      DBMS_OUTPUT.PUT_LINE('Branch 3');
    ELSE
      RAISE CASE_NOT_FOUND;
  END CASE;
EXCEPTION
  WHEN CASE_NOT_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No branch matches the value.');

  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Sample Output:

No branch matches the value.

Explanation:

The said code in Oracle's PL/SQL that demonstrates the handling of the CASE_NOT_FOUND exception and how to gracefully handle situations where the given value does not match any of the expected cases in a CASE statement.

A variable v_value is initialized with the value 5. The CASE statement checks the value of v_value and execute the corresponding branch based on the matching condition. The code includes three branches for values 1, 2, and 3, respectively. If none of these conditions are met, the ELSE block is executed, which raises the CASE_NOT_FOUND exception.

It then displays the message "No branch matches the value." If any other exception occurs, the code catches it in the WHEN OTHERS block and displays an error message along with the SQLERRM.

Flowchart:

Previous: Handling COLLECTION_IS_NULL exception.
Next: Handling INVALID_TRANSACTION exception in PL/SQL with Example Code.