Handling Exceptions
Three categories
Predefined exceptions have names and numbers (NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, DUP_VAL_ON_INDEX, INVALID_NUMBER, VALUE_ERROR). Non-predefined exceptions are ORA- errors without a name; bind one with PRAGMA EXCEPTION_INIT(e_name, -2292). User-defined exceptions are declared in the block and raised with RAISE e_name.
Handler behaviour
EXCEPTION WHEN e1 OR e2 THEN ... WHEN OTHERS THEN .... A handler catches only exceptions raised in the executable part of its own block. An exception raised in a nested block that has no handler propagates outward; the inner block terminates and the enclosing handler runs. WHEN OTHERS must be last. Its biggest trap is swallowing an error without re-raising, so the caller believes the work succeeded.
What is not automatic
An exception handler does not undo DML that already ran in the block - the statements are still part of the transaction. A handler only rolls back if you code ROLLBACK or ROLLBACK TO savepoint. Consequently a handler that logs and continues leaves partial changes in place.
Application errors
RAISE_APPLICATION_ERROR(num, msg [, keep_errors]) accepts numbers from -20000 to -20999; outside that range raises ORA-21000. SQLCODE returns the numeric code and SQLERRM the message (truncated at 512 bytes); DBMS_UTILITY.FORMAT_ERROR_STACK gives the full stack.
Rule of thumb: every WHEN OTHERS should log and then RAISE unless it genuinely resolves the problem.