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.