Before going through this article it will be helpful to read SQL – Transaction Statements -Transaction Management… and Savepoints In SQL Transactions. – SAVEPOINT to know more about SQL transactions.
Autonomous transactions are independent transactions that can be called from within another transaction.
This allows you to leave the calling transaction and do some SQL operations, commit or rollback those operations, and return to the calling transaction’s context and continue with that transaction.Only committed data can be shared by both transactions.
The following is a simple example to explain Autonomous transactions.
Create a table using the following script.
SQL> create table Mytable (name varchar2(100), msg varchar2(500), id number);
Now create a small procedure using the following script.
The procedure will be executed under an autonomous transaction.
(This transaction will be independent of the parent transaction.)
create or replace procedure Autonomous_Example is PRAGMA AUTONOMOUS_TRANSACTION; begin insert into MyTable(name,msg,id) values('Abcd2','Inside Autonomous',4); insert into MyTable(name,msg,id) values('Abcd2','Inside Autonomous',5); commit; end Autonomous_Example;
Here the keyword PRAGMA is a compiler directive.
Now executes the following TEST script
-- Created on 11/4/2008 by Globinch PLSQLTIPS declare -- Local variables here</span> begin -- Test statements here insert into MyTable(name,msg,id) values('Abcd','Outside Autonomous',1); Autonomous_Example(); commit; end;
This will create 3 rows in MyTable.
Now remove the records from MyTable
SQL> delete from mytable; 3 rows deleted SQL> commit; Commit complete SQL>
Now change the TEST script as follows
-- Created on 11/4/2008 by Globinch PLSQLTIPS declare -- Local variables here begin -- Test statements here insert into MyTable(name,msg,id) values('Abcd','Outside Autonomous',1); Autonomous_Example(); rollback; end;
See the above script,we have added a rollback statement
instead of COMMIT to rollback the current transaction.
This will not have any effect to the autonomous transaction in procedure Autonomous_Example.
This will create only 2 rows in MyTable as seen below.
This is because the ROLLBACK performed in the script (Main transaction) has no effect on the
Autonomous Transactions declared inside the procedure Autonomous_Example.
There are no limits on how many levels of autonomous transactions can be called in a chain.
The autonomous transaction can be declared in the following ,
1.Stored procedure
2.Function
3.Package
4.Type method
5.Top-level anonymous block
Also Read,
- Savepoints In SQL Transactions. – SAVEPOINT
- Oracle Tablespace, and Datafiles – Introduction
- Formatting date in SQL (Oracle) -Simple date forma…
- FINDING database objects, finding valid and INVALI…
- Oracle Data types
Technorati Tags:
Autonomous Transactions, PL/SQL, Autonomous blocks, PRAGMA AUTONOMOUS_TRANSACTION, PRAGMA, AUTONOMOUS_TRANSACTION
Pingback: Oracle timestamp format milliseconds| Oracle Date format | SQL and PLSQL
Pingback: Database Schema | Oracle Schema Objects | What is database schema? | SQL and PLSQL