PL/SQL Exception Handling Best Practices: Error Logging That Actually Helps

When a production ERP job fails at 2 a.m., the quality of your error handling decides whether the fix takes ten minutes or two days. Good PL/SQL exception handling does three things: it stops bad data from being saved, it records exactly what went wrong and where, and it shows the user a message they can act on. This guide covers the patterns we use in real business applications.

1. Only catch what you can handle

An exception handler should exist for a reason: to translate an error into a clear message, to log it, to clean up, or to take an alternative path. If none of these apply, let the exception propagate to the caller. Catching an error and doing nothing hides problems until they cause bigger ones.

2. Never write WHEN OTHERS THEN NULL

-- Do not do this
BEGIN
  post_invoice(p_invoice_id => 1045);
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;

This silently swallows every error, including disk full, deadlocks and constraint violations. The user believes the invoice was posted; it was not. If you must catch OTHERS, log the error and re-raise it.

3. Handle expected errors by name

Some errors are part of normal business flow. Handle those specifically and turn them into meaningful messages:

BEGIN
  SELECT credit_limit
    INTO l_limit
    FROM customers
   WHERE customer_id = p_customer_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20010, 'Customer ' || p_customer_id || ' does not exist.');
END;

Reserve a range of application error numbers (for example -20001 to -20999) and document what each means, so support staff and front-end code can react consistently.

4. Build a central error log

A single log table makes production support much easier:

CREATE TABLE app_error_log (
  log_id        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  log_time      TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
  module_name   VARCHAR2(200),
  error_code    NUMBER,
  error_message VARCHAR2(4000),
  backtrace     VARCHAR2(4000),
  call_stack    VARCHAR2(4000),
  context_info  VARCHAR2(4000),
  db_user       VARCHAR2(128) DEFAULT USER,
  app_user      VARCHAR2(255)
);

5. Use an autonomous transaction for logging

If the calling code rolls back, a normal insert into the log table would roll back too and the evidence disappears. An autonomous transaction commits the log entry independently:

CREATE OR REPLACE PROCEDURE log_error (
  p_module  IN VARCHAR2,
  p_context IN VARCHAR2 DEFAULT NULL
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO app_error_log
    (module_name, error_code, error_message, backtrace, call_stack, context_info, app_user)
  VALUES
    (p_module,
     SQLCODE,
     SUBSTR(SQLERRM, 1, 4000),
     SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 4000),
     SUBSTR(DBMS_UTILITY.FORMAT_CALL_STACK, 1, 4000),
     SUBSTR(p_context, 1, 4000),
     SYS_CONTEXT('APEX$SESSION', 'APP_USER'));
  COMMIT;
END log_error;
/

Call log_error from inside an exception handler while SQLCODE and SQLERRM still describe the current error. Use autonomous transactions only for logging and auditing – using them for business data can break transactional consistency.

6. Capture the line number with the backtrace

SQLERRM tells you what failed. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE tells you where: the package and line number where the error was originally raised, even after it has passed through several layers. Without it, debugging a large package is guesswork.

7. The log-and-re-raise pattern

PROCEDURE post_invoice (p_invoice_id IN NUMBER) IS
BEGIN
  validate_invoice(p_invoice_id);
  create_journal(p_invoice_id);
  mark_posted(p_invoice_id);
EXCEPTION
  WHEN OTHERS THEN
    log_error(p_module  => 'INVOICE_PKG.POST_INVOICE',
              p_context => 'invoice_id=' || p_invoice_id);
    RAISE;
END post_invoice;

Logging plus RAISE gives you both: a permanent record for support, and a failure that the caller (and the user) can see. Include business keys in the context – an invoice number is far more useful than “an error occurred”.

8. Friendly messages in Oracle APEX

Users should not see raw ORA- messages. APEX lets you define an Error Handling Function at application level. Use it to:

• Show the text of your -20xxx application errors directly.
• Map constraint violations (for example a unique key on invoice number) to readable messages.
• Log unexpected internal errors and show a generic message with a reference number.

9. Housekeeping

• Purge or archive old log rows on a schedule so the table stays small.
• Do not log passwords, card data or other sensitive values in the context column.
• Review the log daily for new error types after each release.
• Email or notify the support team for repeated or critical errors.

Checklist

• No WHEN OTHERS THEN NULL anywhere in the code base
• Expected errors handled by name with clear messages
• Documented application error number range
• Central autonomous error logger with backtrace and context
• Log-and-re-raise in key procedures
• APEX error handling function configured
• Log retention and monitoring in place