Commit an Insert statement in a 'pragma autonomous_transaction' procedure : Autonomous Transaction « PL SQL Statements « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Statements
3. Autonomous Transaction

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

Table created.

SQL>
SQL> create or replace procedure auto_proc
  2  as
  3          pragma autonomous_transaction;
  4  begin
  5          insert into t values ( 'A row for you' );
  6          commit;
  7  end;
  8  /

Procedure created.

SQL>
SQL> create or replace
  2  procedure proc( read_committed in boolean )
  3  as
  4  begin
  5          if ( read_committed ) then
  6                  set transaction isolation level read committed;
  7          else
  8                  set transaction isolation level serializable;
  9          end if;
 10
 11          auto_proc;
 12
 13          for x in ( select * from t ) loop
 14             dbms_output.put_line( x.msg );
 15          end loop;
 16          commit;
 17  end;
 18  /

Procedure created.

SQL>
SQL> exec proc( TRUE )
A row for you

PL/SQL procedure successfully completed.

SQL>
SQL> delete from t;

1 row deleted.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> exec proc( FALSE )

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table t;

Table dropped.

SQL>