Oracle ORA-06519 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06519: Active Autonomous Transaction Detected and Rolled Back

ORA-06519 occurs when a PL/SQL program unit declared with PRAGMA AUTONOMOUS_TRANSACTION exits without explicitly issuing a COMMIT or ROLLBACK. Oracle detects the open transaction, forcibly rolls it back, and raises ORA-06519 to the caller. This error is most commonly seen in autonomous stored procedures, functions, and triggers used for audit logging or independent DML operations.

Top 3 Causes

1. Missing COMMIT or ROLLBACK in the Autonomous Block

The most common cause is simply forgetting to finalize the transaction before the subprogram ends.

-- BAD: Triggers ORA-06519
CREATE OR REPLACE PROCEDURE log_event_bad (p_msg IN VARCHAR2) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO event_log (message, log_date) VALUES (p_msg, SYSDATE);
    -- No COMMIT here -> ORA-06519 raised on exit
END;
/

-- GOOD: Always commit explicitly
CREATE OR REPLACE PROCEDURE log_event_good (p_msg IN VARCHAR2) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO event_log (message, log_date) VALUES (p_msg, SYSDATE);
    COMMIT; -- Required!
END;
/

2. Unhandled Exception Leaves Transaction Open

When a runtime exception is raised inside an autonomous transaction and the EXCEPTION block does not include a ROLLBACK, Oracle rolls back the transaction automatically and raises ORA-06519.

-- BAD: Exception path skips ROLLBACK
CREATE OR REPLACE PROCEDURE risky_log (p_msg IN VARCHAR2) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO event_log (message, log_date) VALUES (p_msg, SYSDATE);
    -- Simulated runtime error
    IF p_msg IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Message cannot be null');
    END IF;
    COMMIT;
    -- If exception fires before COMMIT -> ORA-06519
EXCEPTION
    WHEN OTHERS THEN
        -- Missing ROLLBACK here is the problem
        RAISE;
END;
/

-- GOOD: Always ROLLBACK in exception handler
CREATE OR REPLACE PROCEDURE safe_log (p_msg IN VARCHAR2) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO event_log (message, log_date) VALUES (p_msg, SYSDATE);
    IF p_msg IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Message cannot be null');
    END IF;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK; -- Always ROLLBACK before re-raising
        RAISE;
END;
/

3. Dynamic SQL (EXECUTE IMMEDIATE) Without Guaranteed COMMIT

Using dynamic DML inside an autonomous transaction makes it easy to miss a COMMIT when conditions branch unexpectedly.

-- GOOD: Dynamic SQL with proper transaction control
CREATE OR REPLACE PROCEDURE dynamic_audit (
    p_table IN VARCHAR2,
    p_msg   IN VARCHAR2
) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_sql VARCHAR2(500);
BEGIN
    v_sql := 'INSERT INTO '
             || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table)
             || ' (message, log_date) VALUES (:1, SYSDATE)';

    EXECUTE IMMEDIATE v_sql USING p_msg;

    COMMIT; -- Must commit even after dynamic DML

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        RAISE;
END;
/

Quick Fix Solutions

Always end every code path with COMMIT or ROLLBACK — review every RETURN statement and every branch inside the autonomous block.
Add ROLLBACK to every EXCEPTION handler — treat it as a hard rule: no EXCEPTION block in an autonomous transaction is complete without a ROLLBACK.
Use a standard template for all autonomous transaction procedures to enforce consistent structure across the team.

Prevention Tips

Establish a coding standard: Every subprogram using PRAGMA AUTONOMOUS_TRANSACTION must follow a template that includes both a COMMIT in the normal path and a ROLLBACK in the exception path. Enforce this in code reviews.
Write unit tests for exception paths: Use frameworks like utPLSQL to simulate failures (e.g., DUP_VAL_ON_INDEX, NO_DATA_FOUND) and verify that all code paths properly terminate the transaction without triggering ORA-06519.

Related Errors

ORA-04092 — Cannot COMMIT in a non-autonomous trigger; often confused with autonomous trigger behavior.
ORA-04091 — Mutating table error; developers sometimes misuse autonomous transactions to work around it, risking ORA-06519.
ORA-06501 — General PL/SQL program error that may accompany internal state issues in autonomous transactions.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community