Autonomous Transactions in PL/SQL -Autonomous Transactions

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

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,

Technorati Tags:
, , , , ,

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

2 thoughts on “Autonomous Transactions in PL/SQL -Autonomous Transactions

  1. Pingback: Oracle timestamp format milliseconds| Oracle Date format | SQL and PLSQL

  2. Pingback: Database Schema | Oracle Schema Objects | What is database schema? | SQL and PLSQL

Leave a Reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page