PL/SQL Exception Handling Best Practices

The three types of Oracle exceptions

Predefined exceptions: named exceptions for common Oracle errors — NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX, VALUE_ERROR, ZERO_DIVIDE.

Non-predefined exceptions: Oracle error codes without predefined names, captured by OTHERS or by declaring them with PRAGMA EXCEPTION_INIT.

User-defined exceptions: custom business logic exceptions you raise explicitly.

Basic structure

BEGIN
  -- code
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    handle_not_found;
  WHEN DUP_VAL_ON_INDEX THEN
    handle_duplicate;
  WHEN OTHERS THEN
    log_error(SQLCODE, SQLERRM);
    RAISE; -- re-raise after logging
END;

PRAGMA EXCEPTION_INIT for specific ORA- errors

DECLARE
  e_table_locked EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_table_locked, -54);
BEGIN
  UPDATE heavy_table SET status = 'P' WHERE ...;
EXCEPTION
  WHEN e_table_locked THEN
    retry_later;
END;

User-defined business exceptions

DECLARE
  e_budget_exceeded EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_budget_exceeded, -20001);
BEGIN
  IF v_amount > v_budget THEN
    RAISE_APPLICATION_ERROR(-20001, 'Budget exceeded: amount ' || v_amount);
  END IF;
END;

Use error codes -20000 to -20999 for custom application errors.

The OTHERS handler: log, don’t swallow

Never write a silent OTHERS handler:

WHEN OTHERS THEN NULL; -- DON'T DO THIS

Always log SQLCODE and SQLERRM:

WHEN OTHERS THEN
  INSERT INTO error_log VALUES (SYSDATE, SQLCODE, SQLERRM, v_context);
  COMMIT;
  RAISE;

Exception propagation

Exceptions propagate up to the nearest enclosing BEGIN/END block that handles them. If unhandled, they propagate to the caller. Design exception handling at the right level — low-level procedures should often re-raise after logging; high-level procedures should provide user-friendly messages.

Autonomous transactions for error logging

Use PRAGMA AUTONOMOUS_TRANSACTION in your error logging procedure so the log INSERT commits independently, even if the main transaction rolls back.