Autonomous Transactions

Contents:- Autonomous Transactions

Autonomous Transactions

Autonomous transactions allows you to leave the context of the calling transactions, perform an independent transaction.

Example

CREATE TABLE at_test (

  id           NUMBER       NOT NULL,

  description  VARCHAR2(50) NOT NULL

);

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');

INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');

SELECT * FROM at_test;

        ID DESCRIPTION

---------- --------------------------------------------------

         1 Description for 1

         2 Description for 2

Now, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.

DECLARE

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  FOR i IN 3 .. 10 LOOP

    INSERT INTO at_test (id, description)

    VALUES (i, 'Description for ' || i);

  END LOOP;

  COMMIT;

END;

/

PL/SQL procedure successfully completed.

SELECT * FROM at_test;

        ID DESCRIPTION

---------- --------------------------------------------------

         1 Description for 1

         2 Description for 2

         3 Description for 3

         4 Description for 4

         5 Description for 5

         6 Description for 6

         7 Description for 7

         8 Description for 8

         9 Description for 9

        10 Description for 10

SQL> ROLLBACK;

SQL> SELECT * FROM at_test;

        ID DESCRIPTION

---------- --------------------------------------------------

         3 Description for 3

         4 Description for 4

         5 Description for 5

         6 Description for 6

         7 Description for 7

         8 Description for 8

         9 Description for 9

        10 Description for 10

So here inner commit statement of anonymous block does not commit earlier data. It performs an independent transactions.