Pragma Autonomous Transaction

According to Oracle document, the AUTONOMOUS TRANSACTION changes the way a subprogram works within a transaction. A subprogram marked with the Pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction. Pragmas are processed at compile time, not at run time but they pass information to the compiler. The examples shown in the article will make your concept clear if the definition wasn't clear to you. The first time, I read the definition from Oracle document, I wasn't clear until I followed some examples

Syntax:
PRAGMA AUTONOMOUS_TRANSACTION;

When do you use it? If you are write an auditing data to a log table, you want to commit the audit data even if the operation you are auditing later fails; if something goes wrong recording the audit data, you do not want the main operation to be rolled back and vice versa.

Example 1: We have employee table that has 10 records. We will add 1 new employee like we do normally using SQL Insert statement without committing the change. Next, we will use a PL/SQL Pragmatic Autonomous Transaction to Insert 3 other employees who are hired recently. What happens when the Rollback statement is issued after the PL/SQL block is done running?

Employee Table Data: 

 

Adding an employee the traditional way: INSERT

INSERT INTO baniya.emp VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

Adding 3 employees using Pragmatic Autonomous PL/SQL block. 

DECLARE
            PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
            INSERT INTO baniya.emp VALUES(7900, 'JAMES',  'CLERK',  7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO baniya.emp VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO baniya.emp VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
COMMIT; -- either committed or rolled back
END;
/

The pragmatic autonomous transaction should end with a commit or a rollback statement. Next, Issue Rollback statement. What do you think is going to happen? Will it Rollback all the four employees you added? Let’s find out.

ROLLBACK;
SELECT  * FROM baniya.employee;

Output:

We inserted 4 employees but we are left with 13 employees. The employee that was added using pragmatic autonomous did not rollback because the autonomous transaction we created was independent and had commit statement within the PL/SQL block. The Rollback statement we issued only impacted the SQL statement without autonomous transaction.

Example 2:  In this example, we will create two procedures. The first procedure is an autonomous transaction that let’s your insert employee. The second procedure is a main that calls the first autonomous translations.

Procedure 1:  Autonomous Transaction to Insert employee data.

CREATE OR REPLACE PROCEDURE baniya.insert_employee(
                        empno_in     IN        NUMBER,
                        ename_in     IN        VARCHAR2,
                        job_in       IN        VARCHAR2,
                        mgr_in       IN        NUMBER,
                        hiredate_in  IN        DATE,
                        sal_in       IN        NUMBER,
                        comm_in      IN        NUMBER,
                        deptno_in    IN        NUMBER)
AS
            PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
            INSERT INTO baniya.emp(
                                    empno,
                                    ename,
                                    job,
                                    mgr,
                                    hiredate,
                                    sal,
                                    comm,
                                    deptno)
                        VALUES(
                                    empno_in,
                                    ename_in,
                                    job_in,
                                    mgr_in,
                                    hiredate_in,
                                    sal_in,
                                    comm_in,
                                    deptno_in);
            COMMIT;
END;
/

Next, we have another transaction that calls this procedure.

Procedure 2:

BEGIN
    DELETE employees;      -- delete everything from employee
    baniya.insert_employee(121, 'BANIYA', 'DBA', 456, '09-NOV-16', 120, 200, 23);
    ROLLBACK;      --issue a rollback
END;
/

The above blocks delete all the employee records from employee table and then insert a new employee using the procedures we created. Next the Rollback statement is issued. What is the impact of rollback here? It will only rollback the delete statement but does not impact the action from the insert_employee procedures because the procedure is autonomous transaction that ends on commit.

Advantages:

1. Once started, an autonomous transaction is fully independent.
2. It shares no locks, resources, or commit-dependencies with the main transaction.
3. You can log events, increment retry counters and so on if even if the main transaction rolls back.
4. Helps you build modular, reusable software components.

Oracle PL/SQL autonomous transactions must explicitly either rollback or commit any changes before exiting and can be:

1. Stand alone procedures or functions
2. Procedures/functions defined in a package (but not nested)
3. Triggers
4. OR schema-level anonymous PL/SQL blocks

Autonomous transactions are commonly used for error logging routines, where the error messages must be preserved, regardless of the the commit/rollback status of the transaction.

Where do people try to use them? I have seen them using in trigger also but this isn't  a good practice and cause complication.