Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 38 ยท Exceptions and Transactions

User-Defined Exceptions and Error Logging in PL/SQL

Declare your own exceptions, bind Oracle errors with PRAGMA EXCEPTION_INIT, use RAISE_APPLICATION_ERROR, log errors autonomously and collect bulk errors with SAVE EXCEPTIONS.

Predefined exceptions cover technical failures. Real applications also have business rules: an order cannot be shipped before it is paid, a withdrawal cannot exceed the balance. This lesson shows how to model such rules as named exceptions, give unnamed Oracle errors a name, send clear error numbers and messages to client applications, record errors for later, and survive errors inside bulk operations.

Oracle cannot run on this site. The PL/SQL is read-only. The runnable examples are Python and SQLite stand-ins that model the same logic.

Declaring and raising your own exceptions

Declare an exception in the declaration section, raise it with RAISE and catch it by name:

SQL
DECLARE
  e_insufficient_funds EXCEPTION;
  v_balance NUMBER := 100;
  v_amount  NUMBER := 250;
BEGIN
  IF v_amount > v_balance THEN
    RAISE e_insufficient_funds;
  END IF;
  v_balance := v_balance - v_amount;
EXCEPTION
  WHEN e_insufficient_funds THEN
    DBMS_OUTPUT.PUT_LINE('Not enough money: balance ' || v_balance || ', requested ' || v_amount);
END;
/

This prints Not enough money: balance 100, requested 250. A user-defined exception raised with plain RAISE has SQLCODE of 1 and message User-Defined Exception. When the exception has to cross a package boundary, declare it in the package specification (e_insufficient_funds EXCEPTION;) so callers can catch it by name: WHEN bank_pkg.e_insufficient_funds THEN.

PRAGMA EXCEPTION_INIT: naming an Oracle error

Many Oracle errors, such as ORA-02292 (child record found), have no predefined name. Bind a name to an error number with PRAGMA EXCEPTION_INIT:

SQL
DECLARE
  e_child_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_child_exists, -2292);
BEGIN
  DELETE FROM departments WHERE department_id = 50;
EXCEPTION
  WHEN e_child_exists THEN
    DBMS_OUTPUT.PUT_LINE('Department still has employees');
END;
/

The code in the pragma is the negative ORA number. Now WHEN e_child_exists replaces a vague WHEN OTHERS plus a check on SQLCODE.

RAISE_APPLICATION_ERROR

To report a business error to a client in a way that carries your own number and text, call RAISE_APPLICATION_ERROR(code, message), with a code between -20000 and -20999:

SQL
CREATE OR REPLACE PROCEDURE withdraw (p_account IN NUMBER, p_amount IN NUMBER) IS
  v_balance accounts.balance%TYPE;
BEGIN
  SELECT balance INTO v_balance FROM accounts WHERE account_id = p_account FOR UPDATE;
  IF p_amount > v_balance THEN
    RAISE_APPLICATION_ERROR(-20101, 'Insufficient funds: balance is ' || v_balance);
  END IF;
  UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_account;
END;
/

The client sees ORA-20101: Insufficient funds: balance is 100. The call ends the procedure immediately and rolls back the effects of the failed call. Agree on a numbering plan for your application (for example -20100 to -20199 for the accounts module) and publish it, and map codes to named exceptions in the callers using PRAGMA EXCEPTION_INIT(e_insufficient_funds, -20101).

Python Named business errors with codes (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Re-raising and exception propagation across layers

Inside a handler you can log and then re-raise so the caller still knows:

SQL
EXCEPTION
  WHEN OTHERS THEN
    error_log_pkg.log_error('withdraw', SQLCODE, SQLERRM,
                            DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;

RAISE; with no name re-raises the exception being handled. Raising a different exception from a handler hides the original unless you carry its message along (use SQLERRM in the text) or save the stack first. Oracle 12c's UTL_CALL_STACK package gives structured access to the error stack.

An error log table with an autonomous transaction

Logging has a trap: if the failing transaction is rolled back, the log row is rolled back with it. The fix is to log inside an autonomous transaction, a separate transaction that commits on its own (see autonomous transactions):

SQL
CREATE TABLE error_log (
  log_id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  logged_at  TIMESTAMP DEFAULT SYSTIMESTAMP,
  source     VARCHAR2(100),
  err_code   NUMBER,
  err_msg    VARCHAR2(4000),
  backtrace  VARCHAR2(4000)
);

CREATE OR REPLACE PROCEDURE log_error (
  p_source IN VARCHAR2, p_code IN NUMBER, p_msg IN VARCHAR2, p_trace IN VARCHAR2
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (source, err_code, err_msg, backtrace)
  VALUES (p_source, p_code, SUBSTR(p_msg, 1, 4000), SUBSTR(p_trace, 1, 4000));
  COMMIT;
END;
/

The SQLite stand-in below shows the effect: the main work is rolled back after an error, but a log row written outside that transaction survives. (A second SQLite connection plays the autonomous transaction.)

Python The log must outlive the rollback (Python and SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Errors inside FORALL: SAVE EXCEPTIONS

A bulk FORALL stops at the first failing row unless you add SAVE EXCEPTIONS. Oracle then runs all iterations, collects the failures and raises ORA-24381 at the end. Details are in the collection SQL%BULK_EXCEPTIONS:

SQL
DECLARE
  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
  TYPE t_ids IS TABLE OF NUMBER;
  v_ids t_ids := t_ids(1, 2, 3);
BEGIN
  FORALL i IN 1 .. v_ids.COUNT SAVE EXCEPTIONS
    INSERT INTO accounts (account_id, balance) VALUES (v_ids(i), 100);
EXCEPTION
  WHEN e_bulk_errors THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Item ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
                           ': ' || SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
    END LOOP;
END;
/

ERROR_INDEX says which element of the collection failed and ERROR_CODE is the positive error number, hence the minus when passed to SQLERRM. Another option for plain SQL DML is LOG ERRORS INTO err$_table, which writes failing rows to an error table and continues (create the table with DBMS_ERRLOG.CREATE_ERROR_LOG).

Design guidelines

  • Raise specific exceptions for expected business failures; leave WHEN OTHERS for the outermost layer (log, then re-raise).
  • Centralize error numbers and names in one package spec.
  • Return clear messages with the offending value, but never include passwords or personal data in error text.
  • Log with an autonomous transaction, and keep the log procedure itself free of failure paths.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which range is reserved for numbers passed to RAISE_APPLICATION_ERROR?

    Oracle reserves -20000 to -20999 for user-defined application errors.
  2. 2. What does PRAGMA EXCEPTION_INIT(e_child_exists, -2292) do?

    The pragma binds a declared exception name to an Oracle error number.
  3. 3. Why log errors inside an autonomous transaction?

    An autonomous transaction commits independently, so rolling back the main work does not erase the log.
  4. 4. What does SAVE EXCEPTIONS do in a FORALL?

    All iterations are attempted and every failure is recorded for you to inspect.

Summary

  • Declare exceptions for business rules, put shared ones in a package spec, and bind unnamed Oracle errors with PRAGMA EXCEPTION_INIT.
  • RAISE_APPLICATION_ERROR(-20000..-20999, msg) sends your own code and text to the caller.
  • Log in an autonomous transaction so the record outlives a rollback; re-raise with RAISE; after logging.
  • FORALL ... SAVE EXCEPTIONS and LOG ERRORS let bulk work continue past bad rows.

Next: COMMIT, ROLLBACK and SAVEPOINT