PRAGMA's are simply directives (instructions) for the Oracle PL/SQL compiler. The pragmaAUTONOMOUS_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)
- 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
INSERT INTO log(msg_id ,log_msg,time_of_msg)
VALUES (log_seq.NEXTVAL,msg ,SYSDATE);
COMMIT; -- must commit or rollback
Next, we have another transaction that calls this procedure.
log_msg('Deleting all employees');
log_msg('after rollback of delete employees');
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.