Pragma Autonomous Transaction in Oracle PL/SQL | Complete Overview

Pragma Autonomous Transaction in Oracle PL/SQL

A Pragma Autonomous Transaction in Oracle PL/SQL allows you to run a transaction independently of the main transaction. Simply put, it means that a piece of code can execute some actions (like inserting or updating data) without being affected by the success or failure of the main transaction. These actions can be committed or rolled back separately.

Why use it?
Imagine you're logging errors. Even if the main transaction fails, you still want to record the error message in the log table. An autonomous transaction lets you do this because it operates independently.

Syntax of Pragma Autonomous Transaction

In Oracle PL/SQL, you declare a block of code as an autonomous transaction using the following syntax:

PRAGMA AUTONOMOUS_TRANSACTION;

This directive is placed inside the declaration section of a procedure, function, or trigger to indicate that the code block will run as an independent transaction.

How It Works:

1.Declaration: You declare PRAGMA AUTONOMOUS_TRANSACTION; at the top of a PL/SQL block (like a procedure, function, or trigger).

2.Independent Execution: When the code runs, it behaves as a separate transaction from the main code that called it. This means it can COMMIT or ROLLBACK changes without affecting the main transaction.

3.Commit or Rollback Required: Since it operates independently, you must explicitly commit or roll back within the autonomous transaction block.

Example Explanation

Here's an example to understand it better:

CREATE OR REPLACE PROCEDURE log_error (error_message VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    -- Insert error message into a log table
    INSERT INTO error_log (message, log_time) VALUES (error_message, SYSDATE);

    -- Commit the transaction, so it saves independently
    COMMIT;
END;

Explanation:

• 1. PRAGMA AUTONOMOUS_TRANSACTION; declares the procedure as an autonomous transaction.
• 2. When you call log_error, it will insert a record into error_log and commit it immediately.
• 3. Even if the main transaction rolls back, the log entry will stay because it was committed separately.

Key Points

• Independent Transactions: They work separately from the main transaction.
• Use Cases: Typically used for logging, auditing, or performing operations that should not be affected by the success or failure of the main transaction.
• Commit/Rollback Required: Since it's a separate transaction, you must explicitly commit or rollback the changes inside an autonomous transaction.

Example Code - Main transaction

BEGIN
    -- Main Transaction: Start
    INSERT INTO employees (id, name) VALUES (101, 'John Doe');

    -- Call the autonomous procedure to log a message
    log_error('New employee added.');

    -- Something goes wrong, so we roll back the main transaction
    ROLLBACK;

    -- Main Transaction: End
END;

Explanation

1. Main Transaction Begins:

• The main block tries to insert a new employee (John Doe) into the employees table.

2. Calling Autonomous Procedure:

• Before committing, it calls the log_error procedure to log a message ('New employee added.') in the error_log table.
• Since log_error is an autonomous transaction, it runs independently. This means the log entry will be committed right away, no matter what happens in the main transaction.

3. Rollback in Main Transaction:

• Suppose something goes wrong after adding the employee, and we decide to ROLLBACK the main transaction. This will undo the INSERT of 'John Doe'.
• However, the log entry ('New employee added.') remains in the error_log table because it was committed as part of the autonomous transaction.

Key Takeaway

Main Transaction and Autonomous Transaction are separate: Changes in the autonomous transaction (logging the message) are committed independently, even if the main transaction rolls back.

Detail Explanation with sample example and data

Let's create an example to demonstrate how a main transaction operates alongside an autonomous transaction, using two tables: employees and audit_log (or error log).

1. Create Tables

First, we'll create the necessary tables to hold employee data and log entries.

SQL for Creating Tables

-- Create the employees table
CREATE TABLE employees (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100)
);

-- Create the audit_log table
CREATE TABLE audit_log (
    log_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    message VARCHAR2(255),
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. Insert Sample Data into Employees Table

• Next, we'll insert a few sample records into the employees table.
• SQL for Inserting Sample Data

-- Insert sample employee records
INSERT INTO employees (id, name) VALUES (101, 'John Doe');
INSERT INTO employees (id, name) VALUES (102, 'Jane Smith');
INSERT INTO employees (id, name) VALUES (103, 'Emily Johnson');

3. Create the Autonomous Transaction Procedure

We'll create a procedure to log actions independently using PRAGMA AUTONOMOUS_TRANSACTION.

SQL for Autonomous Transaction Procedure

CREATE OR REPLACE PROCEDURE log_employee_action (log_message VARCHAR2) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    -- Insert a log entry into the audit_log table
    INSERT INTO audit_log (message, log_time) VALUES (log_message, SYSDATE);

    -- Commit the autonomous transaction independently
    COMMIT;
END;

4. Main Transaction Example

Now, let's use a PL/SQL block to demonstrate the main transaction that adds an employee and logs the action.

SQL for Main Transaction Block

BEGIN
    -- Main Transaction: Start
    INSERT INTO employees (id, name) VALUES (104, 'Michael Brown');

    -- Log the action using the autonomous procedure
    log_employee_action('Employee Michael Brown added.');

    -- Simulate an issue and roll back the main transaction
    ROLLBACK;  -- This will undo the above INSERT

    -- Main Transaction: End
END;

Summary of the Example

• The main transaction attempts to insert a new employee into the employees table.
• It also logs the action using an autonomous transaction, which commits the log entry immediately, regardless of the main transaction's outcome.
• When the main transaction rolls back due to an issue, the employee record for Michael Brown is not added, but the log entry remains in the audit_log table, illustrating the independent nature of autonomous transactions.

This complete example showcases how both transactions work together while maintaining their independence!

Useful for Logging and Auditing: This is why autonomous transactions are often used for logging, so you can record important information regardless of other transaction outcomes.