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).