What is the pragma autonomous_transaction in PL/SQL?

by JAMUNA
(HYDERABAD)

From Jamuna, Hyderbad - What is the AUTONOMOUS_TRANSACTION pragma in Oracle PL/SQL?


PRAGMA's are simply directives (instructions) for the Oracle PL/SQL compiler. The pragma AUTONOMOUS_TRANSACTION instructs the compiler to treat the pl/sql block following the pragma as autonomous (independent) from the calling transaction. This means that any changes made to the database in the autonomous transaction are independent of the main transaction and are either committed or rolled back without affecting the main transaction.

Oracle pl/sql autonomous transactions must explicitly either roll back or commit any changes before exiting and can be:-
  • stand alone procedures or functions
  • procedures/functions defined in a package (but not nested)
  • triggers
  • or schema-level anonymous pl/sql blocks

Autonomous transactions are often used for logging errors in Oracle PL/SQL applications. By making the error logging procedure autonomous you ensure that the error message itself is logged in the error log table and is committed whilst the transaction that encountered the error is rolled back. Then when the application has fini9shed (or indeed whilst it's still running) error messages can be examined and corrective action potentially applied.

Let's look at a brief example.

First we declare an anonymous transaction

CREATE OR REPLACE PROCEDURE log_details
(msg IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN

    INSERT INTO log(msg_id         ,log_msg,time_of_msg)
    VALUES         (log_seq.NEXTVAL,msg    ,SYSDATE);

    COMMIT; -- must commit or rollback

END;


Next, we have another transaction that calls this procedure.

BEGIN

    DELETE employees;
    log_msg('Deleting all employees');
    ROLLBACK;
    log_msg('after rollback of delete employees');

END;


After this, the employees table would be unchanged, but there would be 2 (new) rows in the LOG table.

For the details of all the conditions for autonomous transactions see the Oracle PL/SQL Language Reference manual.

You might also want to review our Oracle PL/SQL tutorials especially designed for beginners and see our Oracle training page for formal training courses both on-line and in person.

Comments for What is the pragma autonomous_transaction in PL/SQL?

Average Rating starstarstarstarstar

Click here to add your own comments

Dec 19, 2016
Rating
starstarstarstarstar
Issue with the above code NEW
by: Anonymous

in child block is it log_msg or log_details?
I am not sure if we have any inbuilt function like log_msg();
please correct me.

Dec 19, 2016
Rating
starstarstarstarstar
transaction NEW
by: Anonymous

clearly

Jul 13, 2016
Rating
starstarstarstar
Good one
by: Ramya

Clearly explained

Mar 17, 2016
Rating
starstarstarstarstar
pls note
by: Anonymous

syntax error is there...
u need to put ';' after 'PRAGMA AUTONOMOUS_TRANSACTION '.

Aug 24, 2015
Rating
starstarstarstarstar
wrong procedure call.
by: Narayan

Error in the anonymous pl/sql block which is calling the PRGMA progma procedure to log the error:

The Calling procedure is LOG_DETAILS not LOG_MSG

Aug 19, 2015
Rating
starstarstarstarstar
Useful Info
by: Harini

clearly explained

Jul 20, 2015
Rating
starstarstarstarstar
good explaination
by: Anonymous

easy to understand and really it's very useful concept

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.