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>