Tip
|
Understand PRAGMA Autonomous_transaction in
PL/SQL in Oracle 8i |
|
|
www.SamOraTech.com
By Sameer Wadhwa
|
Autonomous
Transaction is a feature of oracle 8i which
maintains the state of its
transactions and save it , to affect with the commit or rollback of the surrounding transactions.
Here
is the simple example to understand this :-
ora816 SamSQL :>
declare
2 Procedure
InsertInTest_Table_B
3
is
4
BEGIN
5
INSERT into Test_Table_B(x) values (1);
6
Commit;
7
END ;
8
BEGIN
9
INSERT INTO Test_Table_A(x) values (123);
10
InsertInTest_Table_B;
11
Rollback;
12
END;
13 /
PL/SQL procedure
successfully completed.
ora816 SamSQL :>
Select * from Test_Table_A;
X
----------
123
ora816 SamSQL :>
Select * from Test_Table_B;
X
----------
1
Notice in above pl/sql COMMIT at line no 6 , commits the
transaction at line-no 5 and line-no 9. The Rollback at line-no 11
actually did nothing. Commit/ROLLBACK
at nested transactions will commit/rollback all other DML transaction before that. PRAGMA AUTONOMOUS_TRANSACTION
override this behavior.
Let us the see the following example with PRAGMA
AUTONOMOUS_TRANSACTION.
ora816 SamSQL :>
declare
2
Procedure InsertInTest_Table_B
3
is
4
PRAGMA AUTONOMOUS_TRANSACTION;
5
BEGIN
6
INSERT into Test_Table_B(x) values (1);
7
Commit;
8
END ;
9
BEGIN
10
INSERT INTO Test_Table_A(x) values (123);
11
InsertInTest_Table_B;
12
Rollback;
13
END;
14
/
PL/SQL
procedure successfully completed.
ora816 SamSQL :> Select
* from Test_Table_A;
no rows selected
ora816 SamSQL :> Select * from Test_Table_B;
X
----------
1
With
PRAGMA AUTONOMOUS_TRANSACTION , the transaction state maintained independently
. Commit/Rollback of nested transaction will no effect the other transaction.
It is advisable to increase the value of
TRANSACTIONS parameter in the INIT parameter file to allow for the extra
concurrent transaction .
End of Tip
|
Please send comments at Wadhwa_S@Hotmail.com
|
|
|
Copyright © Sameer Wadhwa All Rights Reserved |