Oracle PL/SQL - PL SQL SQL Statement Autonomous Transactions


Consider the following code

     --Begins an autonomous transaction. 
     pragma autonomous_transaction;                                      
     number of statements
     commit;(or rollback;) -- End of transaction 1                            
     number of statements
     commit;(or rollback;) -- End of transaction 2

pragma autonomous_transaction; command indicates that the autonomous transaction starts from the BEGIN statement where the pragma statement is found.

From this point until the end of the transaction, all PL/SQL blocks including functions, procedures, anonymous blocks, and triggers belong to that new transaction.

The END statement doesn't close the autonomous transaction automatically.

The transaction must be closed by issuing a COMMIT, ROLLBACK, or any command including an implicit commit.

If one of these commands isn't executed and the block defined as an autonomous transaction ends, the Oracle RDBMS will roll back the entire transaction and raise an error with the following message: ORA-06519: active autonomous transaction detected and rolled back.

If the transaction ended but the block defined as autonomous didn't finish, the new transaction will be autonomous.

A pragma autonomous transaction can be used in the declaration part of the following:

  • Top-level anonymous blocks
  • Local, standalone, or packaged functions and procedures
  • Database triggers
  • Methods of object types

Related Topic