pragma autonomous_transaction and rollback : Autonomous_transaction « Stored Procedure Function « Oracle PL / SQL






pragma autonomous_transaction and rollback

   

SQL>
SQL> create table t ( msg varchar2(25) );

Table created.

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> 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> begin
  2          insert into t values ( 'Anonymous Block' );
  3          NonAutonomous_Insert;
  4          rollback;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from t;

MSG
-------------------------
Anonymous Block
NonAutonomous Insert

SQL> delete from t;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> begin
  2          insert into t values ( 'Anonymous Block' );
  3          Autonomous_Insert;
  4          rollback;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from t;

MSG
-------------------------
Autonomous Insert

SQL>
SQL> drop table t;

Table dropped.

SQL>

   
    
    
  








Related examples in the same category

1.pragma autonomous_transaction and exception
2.pragma autonomous_transaction and raise exception
3.Mark function with 'pragma autonomous_transaction'