SQL> set echo on
SQL>
SQL> create table t ( msg varchar2(25) );
SQL>
SQL> create or replace procedure Autonomous_Insert
2 as
3 pragma autonomous_transaction;
4 begin
5 insert into t values ( 'Autonomous Insert' );
6 commit;
7 end;
8 /
Procedure created.
SQL>
SQL> create or replace procedure NonAutonomous_Insert
2 as
3 begin
4 insert into t values ( 'NonAutonomous Insert' );
5 commit;
6 end;
7 /
Procedure created.
SQL>
SQL> select * from t;
MSG
-------------------------
Anonymous
SQL>
SQL> begin
2 insert into t values ( 'Anonymous Block' );
3 NonAutonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
MSG
-------------------------
Anonymous
Anonymous Block
NonAutonomous Insert
SQL> delete from t;
3 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL> begin
2 insert into t values ( 'Anonymous Block' );
3 Autonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
MSG
-------------------------
Autonomous Insert
SQL>
SQL> drop table t;
Table dropped.
SQL>