PL/SQL Autonomous Transactions – Syntax, Examples, and Use Cases

Autonomous Transactions in PL/SQL allow a procedure, function, or block to perform operations like COMMIT or ROLLBACK independently of the main (parent) transaction. This is extremely useful for scenarios such as logging, auditing, or error handling, where changes need to be saved even if the main transaction fails or is rolled back.

In PL/SQL Anonymous Blocks, we typically perform operations within a single transaction. But with autonomous transactions, we break out of this behavior for specific needs.

What Are Autonomous Transactions in PL/SQL?

An autonomous transaction starts from within a parent transaction but operates completely independently for transaction control. That means if the main transaction fails or is rolled back, the autonomous transaction can still succeed with a COMMIT.

This makes it ideal for:

Logging user actions (auditing)
Recording debug messages
Capturing failed transaction data

Key Characteristics of Autonomous Transactions

Executes independently of parent transaction
Must be explicitly committed or rolled back
Declared using PRAGMA AUTONOMOUS_TRANSACTION
Commonly used in procedures, functions, triggers, and object types
Parent transaction can fail or roll back, but autonomous logic remains committed

Syntax of Autonomous Transactions

To create an autonomous transaction, add the following pragma in the declaration section of a PL/SQL block:

PRAGMA AUTONOMOUS_TRANSACTION;

Example: Logging with an Autonomous Procedure

Let’s create a procedure that logs activity independently of the main business logic.

CREATE OR REPLACE PROCEDURE logging_ins (
  i_username IN VARCHAR2,
  i_datetime IN TIMESTAMP
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO logging (username, datetime)
  VALUES (i_username, i_datetime);
  COMMIT; -- Important!
END;
/

Why COMMIT Is Important in Autonomous Transactions

Failing to commit or roll back in an autonomous transaction will cause runtime errors, such as:

ORA-06519: active autonomous transaction detected and rolled back

Use Cases and Benefits of Autonomous Transactions

You should use autonomous transactions in PL/SQL when:

You want to log activity even if the parent process fails.
You’re implementing custom auditing.
You need to send notifications without affecting main logic.

This is especially useful when creating PL/SQL Triggers, where changes must be tracked regardless of trigger success

Calling an Autonomous Procedure from a Parent Block

Now let’s build a procedure that performs a DML operation and calls the above autonomous transaction:

CREATE OR REPLACE PROCEDURE book_ins (
  i_isbn IN BOOKS.ISBN%TYPE,
  i_category IN BOOKS.CATEGORY%TYPE,
  i_title IN BOOKS.TITLE%TYPE,
  i_num_pages IN BOOKS.NUM_PAGES%TYPE,
  i_price IN BOOKS.PRICE%TYPE,
  i_copyright IN BOOKS.COPYRIGHT%TYPE,
  i_author1 IN BOOKS.AUTHOR1%TYPE,
  i_author2 IN BOOKS.AUTHOR1%TYPE,
  i_author3 IN BOOKS.AUTHOR1%TYPE
) IS
BEGIN
  INSERT INTO books (...) VALUES (...);
  
  -- Call to autonomous logging
  LOGGING_INS('PLSQL', SYSTIMESTAMP);
  
  -- Rollback parent transaction
  ROLLBACK;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

How to Test Autonomous Transaction Behavior

BEGIN
  book_ins('12345678', 'Oracle Server', 'Advanced Oracle Logging',
           440, 35.99, 2005, 44, NULL, NULL);
END;
/

Output:

LOGGING table will retain the committed log entry.
BOOKS table will show no inserted row due to the rollback in the parent transaction.

Common Pitfall: Missing COMMIT or ROLLBACK

Here's an incorrect version of an autonomous transaction:

CREATE OR REPLACE PROCEDURE logging_ins_error (
  i_username IN VARCHAR2,
  i_datetime IN TIMESTAMP
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO logging (username, datetime)
  VALUES (i_username, i_datetime);
  -- No COMMIT or ROLLBACK!
END;
/

When executed:

EXEC logging_ins_error('PLSQL', SYSTIMESTAMP);

You’ll get:

ORA-06519: active autonomous transaction detected and rolled back

Best Practices for Using Autonomous Transactions

Always end with COMMIT or ROLLBACK
Use for non-critical operations like logging
Avoid placing business logic that must synchronize with parent transactions
Ensure error handling within the block to prevent leaks

👉 Learn more from Oracle’s official PL/SQL AUTONOMOUS_TRANSACTION documentation for syntax and behavior in depth.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.