PL/SQL Autonomous Transactions

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

PL/SQL Autonomous Transactions

Autonomous transactions refer to the ability of PL/SQL temporarily suspend the current transaction and begin another transaction. The second transaction is known as an autonomous transaction, and functions independently from the parent code. This is especially important for PL/SQL exception handling, as we will see in a minute.

A PL/SQL autonomous transaction has the following characteristics:

The child code runs independently of its parent.
The child code can commit or rollback & parent resumes.
The parent code can continue without affecting child work

Autonomous transactions are important because there are many times when you may want to commit or roll back some changes to a table independently of a primary transaction's final outcome. In the example below, we create a procedure called ErrHandler (shown in red), which writes PL/SQ: exception data to an Oracle table. This must be independent of the calling transaction because we want the main transaction to rollback its work, while allowing the ErrHandler code to complete successfully.

Let’s take a closer look at using autonomous transactions for error handling in PL/SQL.

At times, you may want to commit or roll back some changes to a table independently of a primary transaction's final outcome. For a simple example, while running a transaction, you may want to log error messages to a debug table even if the overall transaction rolls back.

An autonomous transaction executes within an autonomous scope. In PL/SQL we use a compiler directive (called a pragma) to tell Oracle that our transaction is autonomous. The AUTONMOUS_TRANSACTIONS pragma instructs the PL/SQL compiler to mark a routine as autonomous (i.e. independent) from the calling code.

For example, assume that you need to log errors into a Oracle database log table. You need to roll back your core transaction because of the error, but you don’t want the error log code to rollback. Here is an example of a PL/SQL error log table:

CREATE TABLE error_log (
    log_code    INTEGER,
    log_mesg    VARCHAR2(2000),
    log_date    DATE,
    log_user    VARCHAR2(50),
    log_mach    VARCHAR2(100),
    log_prog    VARCHAR2(100)
);

Now we can create a Logging Procedure write_log, which inserts a row in the log table when a PL/SQL error occurs. In the code below, we create a procedure called write_log, with an error handler in the EXCEPTION part of the code. Carefully review this code so that you can see where the parent transaction is suspended and the child transaction is executed.

CREATE OR REPLACE PROCEDURE write_log (
      log_code IN INTEGER,
      log_mesg IN VARCHAR2) IS
--
PRAGMA AUTONOMOUS_TRANSACTION;
--
CURSOR sess IS
SELECT machine, program
FROM v$session
WHERE audsid = USERENV('SESSIONID');
--
-- PT = Parent Transaction,
-- CT = Child Autonomous Transaction
--
rec sess%ROWTYPE;
--
BEGIN                      -- PT suspends
   --
   OPEN sess;
   FETCH sess INTO rec;
   CLOSE sess;
   --
   INSERT INTO error_log VALUES ( -- CT begins
          log_code,
          log_mesg,
          SYSDATE,
          USER,
          rec.machine,
          rec.program
   );
   COMMIT;                  -- CT ends
EXCEPTION
WHEN OTHERS THEN
    ROLLBACK;
END;                        -- PT resumes
/

Now that we have an autonomous procedure called write_log, we can create a test procedure called add_emp, with the EXCEPTION handler calling our new write_log procedure.

CREATE OR REPLACE PROCEDURE add_emp IS
BEGIN
INSERT INTO emp (ename)    -- PT begins
    VALUES ('miller');       -- deliberate RI violation
COMMIT;                    -- cannot insert wo EMPNO
EXCEPTION
WHEN OTHERS THEN
write_log (SQLCODE,SQLERRM); -- call autonomous transaction
ROLLBACK;
RAISE;
END;                         -- PT ends
/

Here, the parent transaction (add_emp) will fail, while the child transaction.

(write_log) will proceed independently. Finally, we must test our add_emp procedure and verify that the insert failed, and that the exception handler executed independently, writing the error to our error_log table:

SQL> exec add_emp;
PL/SQL procedure completed successfully.

SQL> select * from emp where ename = ‘miller’;
no rows selected.

SQL> SELECT * FROM error_log;

LOG_CODE LOG_MESG
-------- ------------------------------------------
-1400 ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO")

As we can see, the error is logged in the autonomous transaction, but the main transaction is rolled back.

For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books