Autonomous Transactions in Oracle PL/SQL

What autonomous transactions do

A normal PL/SQL transaction shares commit/rollback scope with its caller. An autonomous transaction runs independently — it can commit or rollback without affecting the calling transaction.

Declare with PRAGMA AUTONOMOUS_TRANSACTION at the subprogram level.

The error logging use case

The most universal use: logging errors even when the main transaction rolls back.

CREATE OR REPLACE PROCEDURE log_error (
  p_code    NUMBER,
  p_message VARCHAR2,
  p_context VARCHAR2
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (log_date, error_code, error_msg, context)
  VALUES (SYSDATE, p_code, p_message, p_context);
  COMMIT;
END;

Now in your main procedure:

BEGIN
  -- business logic
EXCEPTION
  WHEN OTHERS THEN
    log_error(SQLCODE, SQLERRM, 'create_order');
    ROLLBACK; -- main transaction rolls back
    -- but the error log INSERT was already committed
END;

Without PRAGMA AUTONOMOUS_TRANSACTION, the error log INSERT would also be rolled back.

Audit trail pattern

Similarly for audit logging — you want the audit record even if the audited operation fails:

CREATE OR REPLACE PROCEDURE audit_action (
  p_user    VARCHAR2,
  p_action  VARCHAR2,
  p_entity  VARCHAR2,
  p_entity_id NUMBER
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_trail VALUES (SYSDATE, p_user, p_action, p_entity, p_entity_id);
  COMMIT;
END;

Important constraints

• An autonomous transaction must explicitly COMMIT or ROLLBACK before returning — otherwise Oracle raises ORA-06519: active autonomous transaction detected and rolled back
• Cannot see uncommitted changes made by the calling transaction (different session view)
• Should not be used as a workaround for transaction design issues — if you’re using them for anything other than logging/auditing, reconsider your architecture

Performance consideration

Each autonomous transaction subprogram call opens a new transaction context. For high-frequency logging (called thousands of times per minute), batch your log writes rather than committing each one individually.