Transactions - Expert Oracle Database Architecture - page 298

Now let's observe the behavior of the nonautonomous transaction in an anonymous block of PL/SQL code:
EODA@ORA12CR1> begin
2 insert into t values ( 'Anonymous Block' );
3 NonAutonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select * from t;
MSG
-------------------------
Anonymous Block
NonAutonomous Insert
As you can see, the work performed by the anonymous block, its INSERT , was committed by the
NONAUTONOMOUS_INSERT procedure. Both rows of data were committed, so the ROLLBACK command had nothing to
roll back. Compare this to the behavior of the autonomous transaction procedure:
EODA@ORA12CR1> delete from t;
2 rows deleted.
EODA@ORA12CR1> commit;
Commit complete.
EODA@ORA12CR1> begin
2 insert into t values ( 'Anonymous Block' );
3 Autonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select * from t;
MSG
-------------------------
Autonomous Insert
Here, only the work done by and committed in the autonomous transaction persists. The INSERT done in the
anonymous block was rolled back by the ROLLBACK statement on line 4. The autonomous transaction procedure's
COMMIT has no effect on the parent transaction started in the anonymous block. In a nutshell, this captures the essence
of autonomous transactions and what they do.
To summarize, if you COMMIT inside a “normal” procedure, it will make durable not only its own work but also
any outstanding work performed in that session. However, a COMMIT performed in a procedure with an autonomous
transaction will make durable only that procedure's work.