Pragma Autonomous Transaction

PRAGMA
Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

1) Autonomous Transaction

Autonomous Transactions is the child transaction, which are Independent of Parent transactions. In Our Example, p1 is child transaction, which is used in the Parent transaction.

Example: –

CREATE or REPLACE Procedure p1 IS
Pragma Autonomous_transaction;
BEGIN
INSERT INTO TEST_T VALUES (1111,’PHANI1’);
COMMIT;
END;

In the Declaration section, you will declare this Transaction as the Autonomous Transaction.

DECLARE
A NUMBER;
BEGIN
INSERT INTO TEST_T VALUES (2222,’JACK’);
P1;
ROLLBACK;
END;

NOW Table has (1111,’PHANI’) Record. COMMIT in the PROCEDURE P1 have not commit the Outside (p1) DML operations. It will just commit p1 transactions.

The ROLLBACK will not rollback PHANI record, it will just rollback the JACK record.

CREATE or REPLACE Procedure p1 IS
BEGIN
INSERT INTO TEST_T VALUES (1111,’PHANI1’);
COMMIT;
END;

If I remove the Pragma Autonomous_transaction From the declaration section, then this transaction will become the normal transaction. Now if you try to use the same parent transaction as given below.

>> delete from TEST_T;

DECLARE
A NUMBER;
BEGIN
INSERT INTO TEST_T VALUES (2222,’JACK’);
P1; — This transaction has ended with the COMMIT;
ROLLBACK;
END;

After executing the above transaction, you can see BOTH records got Inserted (PHANI and JACK records). Here COMMIT in P1 will commit both transactions (PHANI and JACK Records Insert) And then Rollback. Since, there are no transactions happening between COMMIT and ROLLBACK. Our ROLLBACK is not doing any ROLLBACK.

Note: – IF COMMIT is not given in P1 then, the ROLLBACK will do the ROLLBACK both the INSERT transaction (PHANI Record which is in p1 procedure and JACK Record).