neema

• May 29th, 2006

 

Autonomous transaction is the transaction which acts independantly from the calling part and could commit the process done.

example using prgma autonomous incase of mutation problem happens in a trigger.

pankaj_gauba

 Profile Answers by pankaj_gauba Questions by pankaj_gauba

• Jun 22nd, 2006

 

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 .

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Sep 23rd, 2006

 

Autonomous_transcation is used to create a new session. It is more useful when you want to have log
like:

You want when ever update statement is fired on emp table. You want a entry should be made in
logfile that emptable is attempted to update no matter updation was successful or not

CREATE TABLE EMPLOG(TERMINAL_NAME VARCHAR2(50), UPDATE_DATE DATE);

CREATE OR REPLACE TRIGGER UPDATE_EMP_TRIG

BEFORE UPDATE ON EMP

DECLARE

RMSG VARCHAR2(50) ;

PRAGAMA AUTONOMOUS_TRANSACTION;

BEGIN

SELECT ' EMP TABLE IS UPDATED FROM TERMINAL : '||USERENV('TERMINAL') INTO RMSG

FROM DUAL;

INSERT INTO EMPLOG VALUES(RMSG, SYSDATE);

COMMIT;

END;

/

now if user issues

UPDATE EMP SET SAL = SAL + 400 ;

ROLLBACK;

This query will not effect emp table but "emplog" will have a entry

g_sidhu

 Profile 

• Feb 1st, 2008

 

Autonomous Transaction are use for writing to an error log table. Upon detecting an error in the transaction, you can insert a row into the error log table and commit it, and then roll back the main transaction without losing the insert.

g_sidhu

 Profile 

• Feb 18th, 2008

 

A autonomous transaction starts with the first sql statement of the pl/sql block and ends with a commit. It starts within the context of an another transaction called parent transaction and independent of it (parent transaction).To make a transaction autonomous u have to declare PRAGMA AUTONOMOUS_TRANSACTION at the beginning.

The main adv. of using PRAGMA AUTONOMOUS_TRANSATION is that weather the transaction made by the parent may be rolled back due to some error, the autonomous transaction has no effect on it. Suppose if there is any error in autonomous transaction … then >>>> what happen? ? ? don’t worry***** It will save all the transactions just before the error occurred. Only the last transaction that has error will be rolled back only if there is no error handler.

shinehere

 Profile Answers by shinehere 

• Feb 24th, 2008

 

An autonomous transaction is a transaction that is started within the context of another transaction, called the parent transaction, but is independent of it.

An autonomous transaction can be committed or rolled back regardless of the state of the parent transaction.

We can specify a transaction as autonomous using a PRAGMA.

PRAGMA AUTONOMOUS TRANSACTION