Pragama is compile directive .

Example of Autonomous transactions

Suppose you are updating value from table and you don't have update trigger on that table

but still you want to maintain a log entry for this update in seprate table.

You can write a procedure and call that procedure to do this . But you can not use COMMIT in this called procedure because it will save the entire transaction.

To avoid this you can delclare this procedure as autonomous transaction procedure so that the execution of this procedure will be treated as totally diff. transaction and you can issue commit in called procedure without affecting the main transaction.

prgma is a compiler directive it takes the oracle description error name from database for oracle error code.

Example of autonous transaction:

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 .

can we change the order of procedure parameter while calling procedure?
Yes we can change the order it is called Named Notation

• Combination: List some of the actual parameters as positional and some as named.