Explain PRAGMA EXCEPTION_INIT in PL/SQL

How it works

PRAGMA EXCEPTION_INIT(exception_name, error_number) binds a named exception you've declared to a specific ORA- error number that has no predefined name of its own — so it can be caught by name instead of writing a generic WHEN OTHERS and inspecting SQLCODE. It's a compile-time association, made once in the DECLARE section right after the exception itself is declared.

DECLARE
  cannot_insert_null EXCEPTION;
  PRAGMA EXCEPTION_INIT(cannot_insert_null, -1400); -- ORA-01400
BEGIN
  INSERT INTO employees (employee_id, last_name) VALUES (500, NULL);
EXCEPTION
  WHEN cannot_insert_null THEN
    DBMS_OUTPUT.PUT_LINE('A required column was left NULL.');
END;
/

Related Questions