Autonomous Transactions

An autonomous transaction is an independent transaction that runs within a parent transaction but commits or rolls back independently of it. Even if the parent transaction rolls back, work done in an autonomous transaction is permanent.

The PRAGMA AUTONOMOUS_TRANSACTION

Declare a subprogram as autonomous by adding the pragma to its declaration section:

CREATE OR REPLACE PROCEDURE log_error_autonomous(
    p_module   IN VARCHAR2,
    p_message  IN VARCHAR2,
    p_sqlcode  IN NUMBER DEFAULT SQLCODE,
    p_sqlerrm  IN VARCHAR2 DEFAULT SQLERRM
) IS
    PRAGMA AUTONOMOUS_TRANSACTION;   -- marks this as a separate transaction
BEGIN
    INSERT INTO error_log (
        log_id, log_time, module_name,
        error_message, error_code, error_text, db_user
    ) VALUES (
        error_log_seq.NEXTVAL,
        SYSTIMESTAMP,
        p_module,
        p_message,
        p_sqlcode,
        p_sqlerrm,
        SYS_CONTEXT('USERENV', 'SESSION_USER')
    );

    COMMIT;  -- MUST commit or rollback before returning
END log_error_autonomous;
/

Why This Matters: Logging That Survives Rollback

Without an autonomous transaction, any log record written inside a failing transaction is lost when the transaction rolls back:

CREATE OR REPLACE PROCEDURE transfer_funds(
    p_from_acct IN NUMBER,
    p_to_acct   IN NUMBER,
    p_amount    IN NUMBER
) IS
BEGIN
    -- Debit
    UPDATE accounts SET balance = balance - p_amount
    WHERE  account_id = p_from_acct;

    IF SQL%NOTFOUND THEN
        log_error_autonomous('TRANSFER_FUNDS', 'Source account not found');
        RAISE_APPLICATION_ERROR(-20401, 'Source account ' || p_from_acct || ' not found');
    END IF;

    -- Credit
    UPDATE accounts SET balance = balance + p_amount
    WHERE  account_id = p_to_acct;

    IF SQL%NOTFOUND THEN
        log_error_autonomous('TRANSFER_FUNDS', 'Target account not found');
        RAISE_APPLICATION_ERROR(-20402, 'Target account ' || p_to_acct || ' not found');
    END IF;

    COMMIT;  -- commit the transfer
    log_error_autonomous('TRANSFER_FUNDS',
        'Transferred $' || p_amount || ' from ' || p_from_acct || ' to ' || p_to_acct);

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;   -- rolls back the transfer — but NOT the autonomous log!
        RAISE;
END transfer_funds;
/

The key insight: when ROLLBACK executes in transfer_funds, the UPDATE on accounts is undone — but the INSERT into error_log (done in the autonomous transaction) is already committed and permanent.

Creating an Audit Log Table

CREATE TABLE emp_audit_log (
    audit_id      NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    audit_time    TIMESTAMP DEFAULT SYSTIMESTAMP,
    action        VARCHAR2(20),
    emp_id        NUMBER,
    old_salary    NUMBER,
    new_salary    NUMBER,
    changed_by    VARCHAR2(50),
    txn_committed CHAR(1) DEFAULT 'Y'  -- always Y for autonomous
);

Autonomous Trigger for Audit

Triggers can also be autonomous — useful when you need to write audit records even if the triggering transaction rolls back:

CREATE OR REPLACE TRIGGER trg_emp_salary_audit_auto
AFTER UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO emp_audit_log (action, emp_id, old_salary, new_salary, changed_by)
    VALUES ('SALARY_UPDATE', :OLD.employee_id, :OLD.salary, :NEW.salary,
            SYS_CONTEXT('USERENV', 'SESSION_USER'));

    COMMIT;  -- commits the audit record independently
END trg_emp_salary_audit_auto;
/

-- Even if the UPDATE is rolled back, the audit record survives
BEGIN
    UPDATE employees SET salary = 99999 WHERE employee_id = 100;
    ROLLBACK;  -- undo the UPDATE — but audit log still has the record
END;
/

SELECT audit_id, audit_time, action, old_salary, new_salary
FROM   emp_audit_log
WHERE  emp_id = 100
ORDER BY audit_time DESC
FETCH FIRST 5 ROWS ONLY;

Rules for Autonomous Transactions

CREATE OR REPLACE PROCEDURE demo_autonomous IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO temp_log VALUES ('test');
    -- MUST end with COMMIT or ROLLBACK:
    COMMIT;
    -- If you reach END without COMMIT/ROLLBACK:
    -- ORA-06519: active autonomous transaction detected and rolled back
END demo_autonomous;
/

Autonomous Functions

Functions can be autonomous too — useful for logging inside SQL-callable functions:

CREATE OR REPLACE FUNCTION log_access(p_table IN VARCHAR2, p_emp_id IN NUMBER)
RETURN NUMBER IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO access_log (table_name, accessed_emp_id, access_time)
    VALUES (p_table, p_emp_id, SYSTIMESTAMP);
    COMMIT;
    RETURN 1;  -- return value allows use in SQL
END log_access;
/

-- Call from SQL — the INSERT is committed even inside a SELECT
SELECT employee_id, first_name,
       log_access('EMPLOYEES', employee_id) AS logged
FROM   employees
WHERE  department_id = 90;

Summary

• PRAGMA AUTONOMOUS_TRANSACTION makes a subprogram run in its own independent transaction.
• The autonomous transaction commits or rolls back independently of the parent.
• Work committed in an autonomous TX survives a parent ROLLBACK — perfect for audit and error logging.
• The autonomous subprogram must COMMIT or ROLLBACK before returning (ORA-06519 otherwise).
• Autonomous transactions cannot see uncommitted parent changes (consistent read snapshot).
• Be alert to deadlocks: avoid locking the same rows the parent has locked.
• Autonomous triggers preserve audit records even when the triggering DML is rolled back.