1. What is a Nested Block?

A nested block is a PL/SQL block written inside another PL/SQL block.
It shares the same transaction context as its parent block.
COMMIT or ROLLBACK in a nested block affects the entire transaction (including the parent block).

Example – Nested Block (emp_source)

Table Setup:


CREATE TABLE emp_source (
    emp_id   NUMBER,
    emp_name VARCHAR2(50)
);

PL/SQL with Nested Block:

BEGIN
    -- Outer block insert
    INSERT INTO emp_source VALUES (1, 'Amit');

    -- Nested block (still part of same transaction)
    DECLARE
        v_name VARCHAR2(50) := 'Neha';
    BEGIN
        INSERT INTO emp_source VALUES (2, v_name);
        -- No COMMIT here, data is not yet permanent
    END;

    -- Rollback entire transaction (both inserts will be undone)
    ROLLBACK;
END;
/

Result: No records are inserted because ROLLBACK in the parent block rolled back changes made by both outer and nested blocks.

2. What is an Autonomous Transaction?

An autonomous transaction is an independent transaction that can COMMIT or ROLLBACK without affecting the main transaction.
Declared using PRAGMA AUTONOMOUS_TRANSACTION inside a procedure, function, or block.
Often used for logging errors or audit records even if the main transaction is rolled back.

Example – Autonomous Transaction (emp_source)

Audit Table for Logging:

CREATE TABLE emp_audit_log (
    log_msg VARCHAR2(100),
    log_time TIMESTAMP DEFAULT SYSTIMESTAMP
);

Procedure for Logging (Autonomous):

CREATE OR REPLACE PROCEDURE log_audit (p_msg VARCHAR2) AS
    PRAGMA AUTONOMOUS_TRANSACTION;  -- Marks this as independent transaction
BEGIN
    INSERT INTO emp_audit_log (log_msg) VALUES (p_msg);
    COMMIT;  -- Commit only log data (doesn't affect main txn)
END;
/

Main Block:

BEGIN
    INSERT INTO emp_source VALUES (3, 'Sunil');

    -- Call autonomous transaction procedure
    log_audit('Inserted Sunil into emp_source');

    ROLLBACK; -- Rolls back insert in emp_source, but NOT the log
END;
/

Result:

emp_source remains empty (because of rollback).
emp_audit_log contains the log entry (because it was committed inside the autonomous transaction).
Note : If PRAGMA AUTONOMOUS_TRANSACTION line is commited then it will commit the data in log_audit table , But here by default emp_source table also gets committed that is why to make it inner block independent PRAGMA AUTONOMOUS_TRANSACTION is required . Also PRAGMA AUTONOMOUS_TRANSACTION is always named PLSQL Block

Key Differences Table

Feature	Nested Block	Autonomous Transaction
Transaction Context	Shares parent's transaction	Independent transaction
Commit/Rollback Effect	Affects both parent & child	Affects only the autonomous block
Usage	Code modularization	Logging, auditing, error handling
Declaration	Just a BEGIN...END block	`PRAGMA AUTONOMOUS_TRANSACTION`

PRAGMA AUTONOMOUS_TRANSACTION Real Life Examples:

1. Banking Transactions (Audit Logging)

When transferring money between accounts, if the main transaction fails (e.g., insufficient balance), the failure must still be logged in an audit table.
Without an autonomous transaction, if the main transaction is rolled back, the log would also be rolled back.
PRAGMA AUTONOMOUS_TRANSACTION ensures the log entry is committed independently, preserving audit trails for compliance.

2. E-Commerce Order Management

While placing an order, multiple tables like orders, inventory, and payments are updated.
If payment fails, the order is rolled back, but error logs or user activity (e.g., "Payment failed due to timeout") should still be stored.
Autonomous transactions help persist these logs even when the main order transaction fails.

3. Telecom Billing Systems

In call or data usage recording, every call must be logged even if rating (pricing) fails due to some error.
The autonomous transaction ensures the CDR (Call Detail Record) is captured and committed regardless of rating errors.

4. ERP Systems (Audit of Critical Config Changes)

When an admin changes tax or pricing configurations, a rollback might happen due to validation errors.
Still, an audit trail of the attempted changes is required for compliance and troubleshooting.
PRAGMA AUTONOMOUS_TRANSACTION allows committing the audit log independently.

5. Fraud Detection & Security Logging

If suspicious activity (e.g., invalid login attempts) occurs during a transaction, the security log must be committed immediately, even if the main transaction fails.
Autonomous transactions ensure that security alerts are never rolled back.

Oracle PLSQL Simplified

Nested Block v/s PRAGMA Autonomous Transaction