Autonomous Insert or nonautomous insert : transaction « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. transaction

SQL> set echo on
SQL>
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>
SQL> drop table t;

Table dropped.

SQL>