Explain user-defined exceptions in PL/SQL

How it works

Declare a custom exception by name in the DECLARE section (of type EXCEPTION), RAISE it explicitly wherever a business rule is violated, and catch it in a WHEN clause exactly like a predefined exception. This is how application-specific error conditions — ones with no corresponding ORA- error of their own — get signaled cleanly.

DECLARE
  insufficient_funds EXCEPTION;
  v_balance NUMBER := 100;
  v_withdrawal NUMBER := 500;
BEGIN
  IF v_withdrawal > v_balance THEN
    RAISE insufficient_funds;
  END IF;
EXCEPTION
  WHEN insufficient_funds THEN
    DBMS_OUTPUT.PUT_LINE('Withdrawal exceeds available balance.');
END;
/

Related Questions