The transaction will be complete under the following scenarios.

When any of the above statements is issued (except SAVEPOINT) When DDL statements are issued. (DDL are auto-commit statements) WHEN DCL statements are issued. (DCL are auto-commit statements)

What is Autonomous Transaction

In PL/SQL, all the modifications done on data will be termed as a transaction. A transaction is considered as complete when the save/discard is applied to it. If no save/discard is given, then the transaction will not be considered as complete and the modifications done on the data will not be made permanent on the server. Irrespective of some modifications done during a session, PL/SQL will treat the whole modification as a single transaction and saving/discard this transaction affects to the entire pending changes in that session. Autonomous Transaction provides a functionality to the developer in which it allows to do changes in a separate transaction and to save/discard that particular transaction without affecting the main session transaction.

This autonomous transaction can be specified at subprogram level. To make any subprogram to work in a different transaction, the keyword ‘PRAGMA AUTONOMOUS_TRANSATION’ should be given in the declarative section of that block. It will instruct that compiler to treat this as the separate transaction and saving/discarding inside this block will not reflect in the main transaction. Issuing COMMIT or ROLLBACK is mandatory before going out of this autonomous transaction to the main transaction because at any time only one transaction can be active. So once we made an autonomous transaction we need to save it and complete the transaction then only we can move back to the main transaction.

Syntax:

In the above syntax, the block has been made as an autonomous transaction.

Example 1: In this example, we are going to understand how the autonomous transaction is working. Output

Code Explanation:

Code line 2: Declaring l_salary as NUMBER. Code line 3: Declaring nested_block procedure Code line 4: Making nested_block procedure as ‘AUTONOMOUS_TRANSACTION’. Code line 7-9: Increasing the salary for employee number 1002 by 15000. Code line 10: Committing the transaction. Code line 13-16: Printing the salary details of employee 1001 and 1002 before changes. Code line 17-19: Increasing the salary for employee number 1001 by 5000. Code line 20: Calling the nested_block procedure; Code line 21: Discarding the main transaction. Code line 22-25: Printing the salary details of employee 1001 and 1002 after changes. The salary increase for employee number 1001 is not reflected because the main transaction has been discarded. The salary increase for employee number 1002 is reflected because that block has been made as a separate transaction and saved at the end. So irrespective of the save/discard at main transaction the changes at autonomous transaction has been saved without affecting the main transaction changes.