Exceptions, Error Stacks, Logging, Assertions, and Testable PL/SQL Design

Learning outcomes

A ServiceHub package catches every exception with WHEN OTHERS THEN NULL, returns “success,” and leaves the incident team with no error location. Another routine logs errors by issuing COMMIT inside the business transaction, accidentally committing work the caller intended to roll back. Reliable PL/SQL error handling has two goals: preserve failure semantics for the caller and add diagnostics without changing transaction meaning.

1. Exception handlers are control flow for failures—not a place to hide them

PL/SQL raises internally defined errors such as constraint violations and provides names for many common exceptions such as NO_DATA_FOUND, TOO_MANY_ROWS, and ZERO_DIVIDE. You can declare your own exception and raise it explicitly, or associate a name with an Oracle error number using PRAGMA EXCEPTION_INIT.

DECLARE  l_amount servicehub_error_case.amount%TYPE;BEGIN  SELECT amount  INTO l_amount  FROM servicehub_error_case  WHERE case_id = -1;EXCEPTION  WHEN NO_DATA_FOUND THEN    RAISE_APPLICATION_ERROR(      -20010,      'ServiceHub case does not exist'    );END;/

Application error numbers passed to RAISE_APPLICATION_ERROR use Oracle's application range. Treat stable codes/messages as an API contract when clients depend on them.

2. RAISE preserves the current exception

Inside an exception handler, plain RAISE; reraises the currently handled exception. This is the safest default when the current layer cannot fully recover. Swallowing an exception or replacing it with a generic one can destroy original SQL and call-site information.

BEGIN  UPDATE servicehub_error_case  SET amount = amount / 0  WHERE case_id = 1;EXCEPTION  WHEN OTHERS THEN    DBMS_OUTPUT.PUT_LINE(      DBMS_UTILITY.FORMAT_ERROR_STACK    );    DBMS_OUTPUT.PUT_LINE(      DBMS_UTILITY.FORMAT_ERROR_BACKTRACE    );    RAISE;END;/

3. SQLCODE/SQLERRM are useful but not the richest normal diagnostics

SQLCODE returns the current error number. SQLERRM returns an error message and is useful for individual SQL%BULK_EXCEPTIONS codes. For normal exception stacks, current Oracle guidance recommends DBMS_UTILITY.FORMAT_ERROR_STACK. FORMAT_ERROR_BACKTRACE reports where the exception was originally raised even when invoked from an outer handler.

EXCEPTION  WHEN OTHERS THEN    l_code      := SQLCODE;    l_stack     := DBMS_UTILITY.FORMAT_ERROR_STACK;    l_backtrace := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;    -- log or return captured values    RAISE;

4. Deliberately wrong: commit business work to make an error log durable

An exception handler that inserts an error row and executes COMMIT in the same transaction also commits every prior successful business change in that transaction. That violates caller-controlled transaction semantics.

EXCEPTION  WHEN OTHERS THEN    INSERT INTO servicehub_error_log(error_stack)    VALUES (SQLERRM);    COMMIT; -- Also commits business changes in this transaction.    RAISE;

Safer options are to return diagnostics to the application and log outside the transaction, use platform/application observability, or use a narrowly designed autonomous logging routine whose transaction contains only the log record.

5. Autonomous logging is independent—use that independence carefully

An autonomous transaction suspends the main transaction and commits or rolls back independently. That makes it suitable for constrained error logging because a later rollback of business work does not erase the log. The same independence is also the risk: the log can outlive business state and can deadlock if it tries to access resources locked by the suspended parent.

CREATE OR REPLACE PROCEDURE servicehub_write_error (  p_error_code  IN NUMBER,  p_error_stack IN VARCHAR2,  p_backtrace   IN VARCHAR2) AUTHID DEFINERIS  PRAGMA AUTONOMOUS_TRANSACTION;BEGIN  INSERT INTO servicehub_error_log(    log_id, logged_at, error_code, error_stack, backtrace  )  VALUES(    servicehub_error_log_seq.NEXTVAL,    SYSTIMESTAMP,    p_error_code,    p_error_stack,    p_backtrace  );  COMMIT;EXCEPTION  WHEN OTHERS THEN    ROLLBACK;    -- Do not re-raise and mask the original application error.END;/

6. Assertions make preconditions executable and testable

An assertion pattern checks assumptions at the boundary of a subprogram and raises an application error before deeper code runs. Keep assertions deterministic, cheap, and focused on invariants that callers can understand.

PROCEDURE assert_positive_amount(p_amount IN NUMBER) ISBEGIN  IF p_amount IS NULL OR p_amount <= 0 THEN    RAISE_APPLICATION_ERROR(      -20021,      'amount must be greater than zero'    );  END IF;END;

Do not duplicate constraints unnecessarily. A database CHECK, NOT NULL, primary key, or foreign key should remain the authoritative relational invariant; PL/SQL assertions add clearer API-level validation where useful.

7. Reproducible lab: package with unit-like checks

BEGIN EXECUTE IMMEDIATE 'DROP TABLE servicehub_error_log PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP TABLE servicehub_error_case PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP SEQUENCE servicehub_error_log_seq';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -2289 THEN RAISE; END IF; END;/CREATE TABLE servicehub_error_case (  case_id NUMBER PRIMARY KEY,  amount  NUMBER(10,2) NOT NULL);CREATE TABLE servicehub_error_log (  log_id       NUMBER PRIMARY KEY,  logged_at    TIMESTAMP WITH TIME ZONE NOT NULL,  error_code   NUMBER,  error_stack  VARCHAR2(2000),  backtrace    VARCHAR2(2000));CREATE SEQUENCE servicehub_error_log_seq;INSERT INTO servicehub_error_case VALUES (1,100);COMMIT;

CREATE OR REPLACE PACKAGE servicehub_error_api AUTHID DEFINER AS  PROCEDURE apply_discount(    p_case_id IN NUMBER,    p_pct     IN NUMBER  );END servicehub_error_api;/CREATE OR REPLACE PACKAGE BODY servicehub_error_api AS  PROCEDURE assert_pct(p_pct IN NUMBER) IS  BEGIN    IF p_pct IS NULL OR p_pct < 0 OR p_pct > 100 THEN      RAISE_APPLICATION_ERROR(        -20022,        'discount percent must be 0..100'      );    END IF;  END;  PROCEDURE apply_discount(    p_case_id IN NUMBER,    p_pct     IN NUMBER  ) IS  BEGIN    assert_pct(p_pct);    UPDATE servicehub_error_case    SET amount = amount * (1 - p_pct/100)    WHERE case_id = p_case_id;    IF SQL%ROWCOUNT = 0 THEN      RAISE_APPLICATION_ERROR(-20023,'unknown case_id');    END IF;  END;END servicehub_error_api;/

SAVEPOINT before_tests;BEGIN  servicehub_error_api.apply_discount(1,10);END;/SELECT amount AS expected_90FROM servicehub_error_caseWHERE case_id=1;BEGIN  servicehub_error_api.apply_discount(1,150);  RAISE_APPLICATION_ERROR(-20999,'test failed: exception expected');EXCEPTION  WHEN OTHERS THEN    IF SQLCODE != -20022 THEN      RAISE;    END IF;    DBMS_OUTPUT.PUT_LINE('expected validation error observed');END;/ROLLBACK TO before_tests;

DROP PACKAGE servicehub_error_api;BEGIN EXECUTE IMMEDIATE 'DROP PROCEDURE servicehub_write_error'; EXCEPTION WHEN OTHERS THEN NULL; END;/DROP TABLE servicehub_error_log PURGE;DROP TABLE servicehub_error_case PURGE;DROP SEQUENCE servicehub_error_log_seq;

8. Production judgment and chapter bridge

Handle exceptions only when the layer can recover, translate them into a deliberate API error, or add diagnostics before reraising. Preserve the original stack/backtrace, keep transaction ownership with the caller, and avoid WHEN OTHERS THEN NULL. Autonomous error logging is a specialized durability tool, not permission to commit arbitrary side effects inside error paths.

No special option, pack, restart, or COMPATIBLE change is required. This completes PL/SQL fundamentals. Chapter 12 can now build on named, testable PL/SQL APIs with triggers, compound triggers, mutating-table behavior, auditing/side effects, and event-driven database logic.

Authoritative references

• PL/SQL Error Handling — exception categories, propagation and handling
• Retrieving Error Code and Error Message — SQLCODE/SQLERRM and FORMAT_ERROR_STACK guidance
• DBMS_UTILITY — FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE
• Raising Exceptions Explicitly — RAISE and RAISE_APPLICATION_ERROR
• Autonomous Transactions — independent transaction semantics