PRAGMA AUTONOMOUS_TRANSACTION with Workflow

PRAGMA AUTONOMOUS_TRANSACTION in the log_user_action procedure, demonstrating how it behaves when an error occurs in the main transaction.

Scenario

We'll modify the example slightly to include an error scenario, showing how the autonomous transaction still commits the log entry even if the main transaction fails.

Step 1: Create or Update the Tables

User Data Table:

CREATE TABLE users (
user_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
username VARCHAR2(50),
email VARCHAR2(100) UNIQUE
);

Audit Log Table:

CREATE TABLE user_audit_log (
log_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
action VARCHAR2(100),
username VARCHAR2(50),
log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Autonomous Logging Procedure

This procedure logs actions and commits independently of the main transaction.

CREATE OR REPLACE PROCEDURE log_user_action(p_action VARCHAR2, p_username VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO user_audit_log (action, username) VALUES (p_action, p_username);
COMMIT; -- Commit the log entry
EXCEPTION
WHEN OTHERS THEN
-- Handle errors within the autonomous transaction
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error while logging action: ' || SQLERRM);
END log_user_action;
/

Step 3: Main Procedure with Error Scenario

Here’s the main procedure that simulates an error (duplicate email) when adding a user.

CREATE OR REPLACE PROCEDURE add_user(p_username VARCHAR2, p_email VARCHAR2) IS
BEGIN
-- Attempt to insert a new user
INSERT INTO users (username, email) VALUES (p_username, p_email);

-- Log the addition of the user
log_user_action('Added user', p_username);

-- Simulate an error: Attempt to add the same email again, which violates the UNIQUE constraint
INSERT INTO users (username, email) VALUES ('duplicate_user', p_email);

COMMIT;  -- Commit the main transaction

EXCEPTION
WHEN OTHERS THEN
-- Handle any errors, rollback the main transaction
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error in main transaction: ' || SQLERRM);
END add_user;
/

Step 4: Execute and Observe Behavior

Run the procedure to see what happens when an error occurs:

BEGIN
add_user('john_doe', 'john.doe@example.com');
END;
/

1. Inserting the First User:

The procedure attempts to insert ('john_doe', 'john.doe@example.com') into the users table. This succeeds.

1. Logging the Action:

The procedure then calls log_user_action('Added user', 'john_doe').

Autonomous Transaction: This logs the action into the user_audit_log and commits it independently.

1. Simulated Error:

The procedure then tries to insert ('duplicate_user', 'john.doe@example.com'), which triggers a UNIQUE constraint violation because john.doe@example.com is already in use.

This causes an error, and the main transaction rolls back.

1. Result:

The insertion of the duplicate user fails, and the users table remains unchanged (no new user added).

However, the action log entry created by log_user_action persists because it was committed as an autonomous transaction.

Table States After Execution:

users Table: | user_id | username | email | |---------|------------|------------------------| | 1 | john_doe | john.doe@example.com |

user_audit_log Table: | log_id | action | username | log_date | |--------|-------------|------------|-----------------------------| | 1 | Added user | john_doe | 2024-10-22 12:34:56.789 |

Explanation of Control Flow with Error:

1. Main Procedure Execution:

Starts by inserting a new user, which succeeds.

Calls the autonomous transaction (log_user_action), which logs the action and commits it independently.

1. Error Occurs:

The main procedure attempts a duplicate insert, causing a constraint violation.

The error triggers the EXCEPTION block, rolling back any changes made within the main transaction.

1. Autonomous Transaction Behavior:

The autonomous transaction (log_user_action) has already committed its changes.

The log entry remains unaffected by the rollback in the main trans
This example shows how PRAGMA AUTONOMOUS_TRANSACTION allows the log_user_action procedure to commit changes independently of the main transaction. Even if the main transaction encounters an error and rolls back, the log entry stays committed. This feature is useful for scenarios like audit logs, where you want to record actions regardless of the success or failure of the primary operation.