The easiest way to
understand autonomous transactions is to see them in action.
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
2 rows selected.
Next, 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
10 rows selected.
As expected, we now
have 10 rows in the table. If we now issue a rollback statement we get the following
result.
ROLLBACK;
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
8 rows selected.
The 2 rows inserted by
our current session (transaction) have been rolled back, while the rows
inserted by the autonomous transactions remain.
The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive
made the anonymous block run in its own transaction, so the internal commit statement
did not affect the calling session.