Example of how AUTONOMOUS_TRANSACTION, it allows one to isolate a DML statements, the DML statements with the AUTONOMOUS transaction are independent of the main transaction. AUTONOMOUS_TRANSACTION can be used in PL/SQL code in procedures, functions, triggers.
-- store procedure that writes message to a log table and defined AUTONOMOUS_TRANSACTION
SQL> CREATE OR REPLACE PROCEDURE scott.write_log(v_msg IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO app_log VALUES (v_msg, SYSDATE);
COMMIT;
END;
/
Procedure created.
-- Shows scott.test and scott.app_log are empty
SQL> select * FROM scott.test;
no rows selected
SQL> select * FROM scott.app_log;
no rows selected
-- Create a main transaction
SQL> insert into scott.test values (1, 2);
1 row created.
-- insert in app_log through the store procedure which uses AUTOMOUS_TRANSACTION
SQL> exec scott.write_log(‘message 1’);
PL/SQL procedure successfully completed.
-- Shows transaction is there in scott.test in that user’s session, note the main transaction is not yet committed
SQL> select * FROM scott.test;
A B
———- ———-
1 2
1 row selected.
-- rollback main transaction and show records after rollback there are no rows in scott.test and scott.app_log has the message that was inserted
SQL> rollback;
Rollback complete.
SQL> select * FROM scott.test;
no rows selected
SQL> select * FROM scott.app_log;
MSG
——————————————————————————–
LOG_DATE
———
message 1
05-FEB-11
Note: The PL/SQL scode that contains AUTONOMOUS_TRANSACTION, the transaction should have a COMMIT or ROLLBACK otherwise it will return “ORA-06519: active autonomous transaction detected and rolled back” when the code executes.
Example:
SQL> CREATE OR REPLACE PROCEDURE scott.write_log(v_msg IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO app_log VALUES (v_msg, SYSDATE);
END;
/
Procedure created.
SQL> exec scott.write_log(‘test1’);
BEGIN scott.write_log(‘test1’); END;
*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at “SCOTT.WRITE_LOG”, line 6
ORA-06512: at line 1
alexlima.com 