pragma AUTONOMOUS_TRANSACTION and lock : transaction « Function Procedure Packages « Oracle PL/SQL Tutorial

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

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

Table created.

SQL>
SQL> create or replace procedure auto_proc
  2  as
  3      pragma AUTONOMOUS_TRANSACTION;
  4      x number;
  5  begin
  6      insert into t values ('AutoProc');
  7      x := 'a'; -- This will fail
  8      commit;
  9  end;
 10  /

Procedure created.

SQL>
SQL> create or replace procedure Regular_Proc
  2  as
  3      x number;
  4  begin
  5      insert into t values ('RegularProc');
  6      x := 'a';
  7      commit;
  8  end;
  9  /

Procedure created.

SQL>
SQL> set serveroutput on
SQL>
SQL> begin
  2      insert into t values ('Anonymous');
  3      auto_proc;
  4  exception
  5      when others then
  6          dbms_output.put_line( 'Caught Error:' );
  7          dbms_output.put_line( sqlerrm );
  8          commit;
  9  end;
 10  /
Caught Error:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

MSG
-------------------------
Anonymous

SQL>
SQL> delete from t;

1 row deleted.

SQL>
SQL> begin
  2      insert into t values ('Anonymous');
  3      regular_proc;
  4  exception
  5      when others then
  6          dbms_output.put_line( 'Caught Error:' );
  7          dbms_output.put_line( sqlerrm );
  8          commit;
  9  end;
 10  /
Caught Error:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

MSG
-------------------------
Anonymous
RegularProc

SQL> drop table t;

Table dropped.

SQL>