Example of AUTONOMOUS_TRANSACTION

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s