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

An autonomous transaction executes within an autonomous scope. An autonomous scope is a routine you mark with the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler to mark a routine as autonomous (independent).

Example

You need to log an error to your database log table. On the other hand, you need to roll back your core transaction because of the error. And you don't want to roll back over other log entries

Create the Log Table: log

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

Now create the Logging Procedure write_log, which inserts a row in the log table when an error occurs. You can use the procedure write_log an an error handler in the EXCEPTION part.

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');
--
-- MT = Main Transaction, AT = Autonomous Transaction
--
rec sess%ROWTYPE;
--
BEGIN -- MT suspends
--
OPEN sess;
FETCH sess INTO rec;
CLOSE sess;
--
INSERT INTO log VALUES ( -- AT begins
log_code,
log_mesg,
SYSDATE,
USER,
rec.machine,
rec.program
);
COMMIT; -- AT ends
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END; -- MT resumes
/

Now create a test procedure with the EXCEPTION handler write_log

CREATE OR REPLACE PROCEDURE add_emp IS
BEGIN
INSERT INTO emp (ename)
VALUES ('miller'); -- MT begins
COMMIT;
EXCEPTION
WHEN OTHERS THEN
write_log (SQLCODE,SQLERRM);
ROLLBACK;
RAISE;
END; -- MT ends
/

Test the execption handler

SQL> exec add_emp;
SQL> SELECT * FROM log;

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

The error is logged in the autonomous transaction, but the main transaction is rolled back.