A naukri.com initiative
Dev
3w
109
Image Credit: Dev
Pragma Autonomous Transaction in Oracle PL/SQL
- A Pragma Autonomous Transaction in Oracle PL/SQL allows you to run a transaction independently of the main transaction.
- You declare a block of code as an autonomous transaction using the following syntax: PRAGMA AUTONOMOUS_TRANSACTION;
- Since it operates independently, you must explicitly commit or roll back the changes inside the autonomous transaction block.
- An autonomous transaction lets you log some actions (like inserting or updating data) even if the main transaction fails.
- Autonomous transactions in Oracle PL/SQL are typically used for logging, auditing, or performing operations that should not be affected by the success or failure of the main transaction.
- Main Transaction and Autonomous Transaction are separate: Changes in the autonomous transaction (logging the message) are committed independently, even if the main transaction rolls back.
- The main transaction attempts to insert a new employee into the employees table. It also logs the action using an autonomous transaction, which commits the log entry immediately, regardless of the main transaction's outcome.
- When the main transaction rolls back due to an issue, the employee record for Michael Brown is not added, but the log entry remains in the audit_log table, illustrating the independent nature of autonomous transactions.
- This is why autonomous transactions are often used for logging so you can record important information regardless of other transaction outcomes.
- PRAGMA AUTONOMOUS_TRANSACTION; declares the procedure as an autonomous transaction.
Read Full Article
6 Likes
For uninterrupted reading, download the app