Another cool and powerful feature that gives us the independence of carrying out a specific task as a separate transaction while inside the main transaction. The main transaction merely suspends for the duration the autonomous transaction runs.

It has great applications and uses while writing pl/sql code.

Autonomous transactions are easy to declare, we need to include the following line in the declaration section of the pl/sql block:

PRAGMA AUTONOMOUS_TRANSACTION;

This pragma tells the compiler that the pl/sql block is independent/autonomous.

Simple demonstration:

When should we make a transaction autonomous:

Whenever we want the changes made by a transaction to be isolated from the main transaction, we make it autonomous.

Below given are some of its uses:

LOGGING MECHANISM: when an error occurs in a pl/sql block, it rolls back all changes made inside the block including any errors logged in the database log table. We should use autonomous transaction to log such errors so that they are retained even after other changes are rolled back.

ISSUE COMMIT/ROLLBACK INSIDE A TRIGGER: we can commit/rollback inside a trigger if we declare it as autonomous.

AVOID MUTATING TABLE TRIGGER ERROR: this error occurs when a row level trigger reads from or writes to the table from which it was fired. If we declare the trigger as autonomous and commit inside it, we will be able to query the table which fired the trigger but the changes that caused the firing of the trigger will not be visible. Only the committed changes will be visible.

RETRY COUNTER: if we want to keep a count of the number of login attempts, we would require a commit that is independent of the main transaction.

So now, go explore yourself folks.

Disclaimer - Views expressed in this blog are author's own and do not necessarily represents the policies of aclnz.com