Logging in PL/SQL that survives ROLLBACK (and 4 mistakes to avoid)

You get a call: last night's batch failed, the transaction rolled back, and now someone asks "what happened?"

You open your log table. It's empty.

If your logging does a plain INSERT inside the same transaction as your business logic, a ROLLBACK removes the log entries together with the data. You lose the information at exactly the moment you need it.

The fix is small: write log entries in their own transaction. Here is the pattern, step by step.

1. The log table

CREATE TABLE app_log (
  log_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  log_ts   TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
  module   VARCHAR2(100 CHAR),
  message  VARCHAR2(1000 CHAR)
);

2. An autonomous logger

CREATE OR REPLACE PROCEDURE log_msg(
  p_module  IN VARCHAR2,
  p_message IN VARCHAR2
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO app_log (module, message)
  VALUES (SUBSTR(p_module, 1, 100), SUBSTR(p_message, 1, 1000));
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;   -- only the autonomous transaction
    NULL;       -- a logging failure must never break business logic
END log_msg;
/

PRAGMA AUTONOMOUS_TRANSACTION makes the procedure run in an independent transaction. Its COMMIT commits only the log row, never the caller's pending work.

3. Prove it works

CREATE TABLE t_demo (id NUMBER);

BEGIN
  INSERT INTO t_demo VALUES (1);
  log_msg('DEMO', 'inserted row 1');
  ROLLBACK;
END;
/

SELECT COUNT(*) FROM t_demo;       -- 0: the business change is gone
SELECT message FROM app_log;       -- 'inserted row 1': the log entry survived

That is the whole idea. Now the part that actually matters in production: the mistakes.

Mistake 1: Logging the error after it was changed

In an exception handler, capture the error information before calling anything else:

EXCEPTION
  WHEN OTHERS THEN
    log_msg('PAY.POST',
            SQLERRM || ' | ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;

Arguments are evaluated before log_msg runs, so you get the right error and the line number where it originated. FORMAT_ERROR_BACKTRACE is what tells you which line failed, not just which procedure caught it.

Mistake 2: Swallowing the exception

Logging an error is not handling it. If your handler logs and then returns normally, the caller thinks everything succeeded. Always RAISE again (or raise a meaningful application error) after logging, unless you are deliberately recovering.

Mistake 3: Putting business work in the autonomous transaction

The logger should only write log rows. If an autonomous transaction touches rows that the calling transaction has locked, it waits for a lock the caller will never release, and you get ORA-00060: deadlock detected. Keep autonomous transactions small and isolated.

Mistake 4: Never cleaning up

A log table that grows forever becomes a performance problem and a storage bill. Delete in batches and schedule it:

CREATE OR REPLACE PROCEDURE purge_log(p_keep_days IN NUMBER DEFAULT 30) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_rows PLS_INTEGER;
BEGIN
  LOOP
    DELETE FROM app_log
     WHERE log_ts < SYSTIMESTAMP - NUMTODSINTERVAL(p_keep_days, 'DAY')
       AND ROWNUM <= 10000;
    v_rows := SQL%ROWCOUNT;
    COMMIT;
    EXIT WHEN v_rows < 10000;
  END LOOP;
END purge_log;
/

Batches keep undo usage small and locks short. Run it from DBMS_SCHEDULER once a night.

What the simple version is missing

This is enough to stop losing your logs. In a real system you will probably also want:

• log levels (DEBUG / INFO / WARN / ERROR) so production isn't flooded
• a run id to group every entry of one batch
• the call stack and error stack stored in separate columns
• timers for slow steps
• a catalogue of application error codes, so RAISE_APPLICATION_ERROR(-20001, ...) isn't scattered through the code with different meanings

I got tired of rebuilding all of that for every project, so I packaged it as a small toolkit: a logger package, an error-handling package with a code catalogue, an installer, a demo, and a utPLSQL test suite. It is tested on Oracle 21c. If you want the ready-made version, it's here: https://sabuhi.gumroad.com/l/something.

Either way, the pattern above is free to use. Which part of PL/SQL boilerplate do you rewrite most often? I'm thinking about what to cover next (bulk processing and audit trails are on my list).