Reusable Error Logging Packages With Autonomous Transactions

Why You Need a Centralized Error Logger

Every production PL/SQL application needs a robust error logging mechanism. When exceptions occur in production, you need a reliable record of what happened, where it happened, and what the system state was at the time. Writing this information to a database table is the most practical approach because it allows you to query, filter, and analyze errors using SQL.

The challenge is that exceptions typically trigger transaction rollbacks, and a rollback would undo your error log entry along with everything else. This is where autonomous transactions become essential.

Autonomous Transactions: Independent Commit Scope

A subprogram declared with PRAGMA AUTONOMOUS_TRANSACTION executes in its own transaction context, independent of the calling transaction. Commits and rollbacks in the autonomous transaction do not affect the calling transaction, and vice versa. This makes autonomous transactions perfect for error logging: you can insert and commit a log record even when the main transaction will be rolled back.

A Production Ready Error Logging Package

-- Error log table
CREATE TABLE app_error_log (
   log_id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   error_timestamp TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
   error_code      NUMBER,
   error_message   VARCHAR2(4000),
   error_backtrace CLOB,
   call_stack      CLOB,
   program_name    VARCHAR2(200),
   context_data    CLOB,
   session_user    VARCHAR2(128),
   module_name     VARCHAR2(256),
   action_name     VARCHAR2(256),
   client_info     VARCHAR2(256)
);

-- Index for common query patterns
CREATE INDEX idx_error_log_ts ON app_error_log(error_timestamp DESC);
CREATE INDEX idx_error_log_code ON app_error_log(error_code);
CREATE INDEX idx_error_log_prog ON app_error_log(program_name);

-- The logging package
CREATE OR REPLACE PACKAGE error_logger AS

   PROCEDURE log_error(
      p_program_name IN VARCHAR2 DEFAULT NULL,
      p_context_data IN VARCHAR2 DEFAULT NULL
   );

   PROCEDURE log_warning(
      p_program_name IN VARCHAR2,
      p_message      IN VARCHAR2,
      p_context_data IN VARCHAR2 DEFAULT NULL
   );

   PROCEDURE purge_old_entries(
      p_days_to_keep IN NUMBER DEFAULT 90
   );

END error_logger;
/

CREATE OR REPLACE PACKAGE BODY error_logger AS

   PROCEDURE log_error(
      p_program_name IN VARCHAR2 DEFAULT NULL,
      p_context_data IN VARCHAR2 DEFAULT NULL
   )
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO app_error_log(
         error_code, error_message, error_backtrace,
         call_stack, program_name, context_data,
         session_user, module_name, action_name, client_info
      ) VALUES (
         SQLCODE,
         SQLERRM,
         DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
         DBMS_UTILITY.FORMAT_CALL_STACK,
         NVL(p_program_name,
             UTL_CALL_STACK.CONCATENATE_SUBPROGRAM(
                UTL_CALL_STACK.SUBPROGRAM(2)
             )
         ),
         p_context_data,
         SYS_CONTEXT('USERENV', 'SESSION_USER'),
         SYS_CONTEXT('USERENV', 'MODULE'),
         SYS_CONTEXT('USERENV', 'ACTION'),
         SYS_CONTEXT('USERENV', 'CLIENT_INFO')
      );
      COMMIT;
   EXCEPTION
      WHEN OTHERS THEN
         -- If logging itself fails, write to alert log as last resort
         SYS.DBMS_SYSTEM.KSDWRT(2,
            'ERROR_LOGGER FAILURE: ' || SQLERRM
         );
         ROLLBACK;
   END log_error;

   PROCEDURE log_warning(
      p_program_name IN VARCHAR2,
      p_message      IN VARCHAR2,
      p_context_data IN VARCHAR2 DEFAULT NULL
   )
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO app_error_log(
         error_code, error_message, program_name,
         context_data, session_user
      ) VALUES (
         0, p_message, p_program_name,
         p_context_data,
         SYS_CONTEXT('USERENV', 'SESSION_USER')
      );
      COMMIT;
   END log_warning;

   PROCEDURE purge_old_entries(
      p_days_to_keep IN NUMBER DEFAULT 90
   )
   IS
   BEGIN
      DELETE FROM app_error_log
       WHERE error_timestamp < SYSTIMESTAMP - NUMTODSINTERVAL(p_days_to_keep, 'DAY');
      COMMIT;
   END purge_old_entries;

END error_logger;
/

Usage Pattern

PROCEDURE transfer_funds(p_from IN NUMBER, p_to IN NUMBER, p_amount IN NUMBER)
IS
BEGIN
   -- Business logic here
   UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from;
   UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to;
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      error_logger.log_error(
         p_context_data => 'From=' || p_from || ', To=' || p_to || ', Amt=' || p_amount
      );
      ROLLBACK;
      RAISE;
END;

Version Notes

Autonomous transactions have been available since Oracle 8i. The UTL_CALL_STACK package used in the example above is available from Oracle 12c onward. For Oracle 11g and earlier, replace the UTL_CALL_STACK call with DBMS_UTILITY.FORMAT_CALL_STACK. The behavior of autonomous transactions has remained consistent from Oracle 19c through 23ai with no significant changes.