Oracle can suspend a transaction and transfer execution control to an independent child transaction.
This child transaction is called an autonomous transaction.
An autonomous transaction is completely independent of the calling transaction.
An autonomous transaction does not share resources, locks, or any commit dependencies with the main transaction.
Autonomous transactions can include just as much functionality as any other database transactions.
Autonomous transactions are useful for creating software components that can be reused in numerous applications.
One advantage of using an autonomous transaction is that DML can be executed and committed, even if the main transaction is rolled back.
Setting up the syntax for an autonomous transaction
Autonomous Transaction Syntax
declare
pragma autonomous_transaction;
begin
...
number of statements
...
commit;(or rollback;) ?End of transaction 1
...
number of statements
...
commit;(or rollback;) ?End of transaction 2
end;
A pragma autonomous transaction is a PL/SQL compiler directive to define an autonomous transaction.
This PL/SQL compiler directive can be used to define:
Top-level anonymous blocks
Local, standalone, or packaged functions and procedures
Database triggers
Methods of object types
Quote from:
Oracle PL/SQL For Dummies (Paperback)
by Michael Rosenblum (Author), Paul Dorsey (Author)
# Paperback: 414 pages
# Publisher: For Dummies (June 13, 2006)
# Language: English
# ISBN-10: 0764599577
# ISBN-13: 978-0764599576