PRAGMA AUTONOMOUS TRANSACTION in ORACLE SQL

In Oracle SQL, the PRAGMA AUTONOMOUS_TRANSACTION directive is used to define a block of code as an autonomous transaction. This means that the transaction can execute independently of the main transaction that invoked it. Any changes made by the autonomous transaction can be committed or rolled back without affecting the main transaction.

Key Points:

An autonomous transaction allows you to perform operations like logging or auditing without impacting the main transaction's outcome.

If the main transaction fails or is rolled back, the changes made by the autonomous transaction will remain committed.

Example Codebase

Here’s a simple example demonstrating how to use PRAGMA AUTONOMOUS_TRANSACTION in a PL/SQL block:

Scenario:

Let's say you have a requirement to log error messages into an audit table whenever an exception occurs in a main transaction.

Step 1: Create the Audit Table

First, create a table to store the log messages:

CREATE TABLE audit_log (
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
log_message VARCHAR2(255),
log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create a Procedure with Autonomous Transaction

Next, create a procedure that uses PRAGMA AUTONOMOUS_TRANSACTION to log messages independently:

CREATE OR REPLACE PROCEDURE log_error(p_message VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION; -- Declare autonomous transaction

BEGIN
INSERT INTO audit_log (log_message) VALUES (p_message);
COMMIT; -- Commit the changes to the audit log
END log_error;
/

Step 3: Main Transaction Procedure

Now, create a main procedure that performs some operations and calls the logging procedure in case of an error:

CREATE OR REPLACE PROCEDURE main_transaction IS
BEGIN
-- Simulating main transaction operations
INSERT INTO some_table (column1) VALUES ('Some value');

-- Simulate an error
RAISE_APPLICATION_ERROR(-20001, 'An error occurred during main transaction');

EXCEPTION
WHEN OTHERS THEN
-- Call the autonomous transaction to log the error
log_error(SQLERRM);
RAISE; -- Reraise the original exception
END main_transaction;
/

How It Works:

1. Main Transaction: When you execute the main_transaction procedure, it attempts to insert a value into some_table.

2. Error Handling: If an error occurs (simulated by RAISE_APPLICATION_ERROR), it jumps to the exception block.

3. Logging: The log_error procedure is called to log the error message. Since it is defined as an autonomous transaction, it commits the error message to the audit_log table independently.

4. Outcome: Even if the main transaction fails and rolls back, the error log entry remains in the audit_log table.

Example Execution:

To execute the main_transaction procedure, simply run:

BEGIN
main_transaction;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Main transaction failed: ' || SQLERRM);
END;
/

Summary

Using PRAGMA AUTONOMOUS_TRANSACTION, you can create independent transactions within your PL/SQL code, allowing for effective logging, error handling, or auditing without affecting the primary transactional flow. This approach is particularly useful for scenarios where you need to ensure that certain actions (like logging) are preserved even in the event of failures in the main transaction.

Comments

[Boopathi]

This is a great explanation of PRAGMA AUTONOMOUS_TRANSACTION! The example with the error logging is especially helpful for understanding how to implement it. I'm definitely going to try using this in my next project!