SQL> 
SQL> Create or replace procedure msg_log (msg VARCHAR2) as
  2        PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4        INSERT INTO msg_tab VALUES (msg);
  5        COMMIT;
  6  END;
  7  /

Procedure created.

SQL> 
SQL> create or replace procedure ins_test2(p1 varchar2, p2 number)
  2  as
  3  begin
  4    insert into test2 values(p1,p2);
  5    msg_log('1 row inserted');
  6  exception
  7    when others then
  8       msg_log(SQLERRM);
  9       raise;
 10  end;
 11  /

Procedure created.

SQL> 
SQL> exec ins_test2('a',8);

PL/SQL procedure successfully completed.

SQL> select * from test2;

C1         C2                                                                   
-- ----------                                                                   
b           2                                                                   
c           3                                                                   
e           1                                                                   
K           6                                                                   
P           0                                                                   
G           0                                                                   
ap          9                                                                   
a           8                                                                   
u           7                                                                   

9 rows selected.

SQL> select * from msg_tab;

C1                                                                              
--------------------------------------------------------------------------------
1 row inserted                                                                  

SQL> 
SQL> -- unique constraint violation
SQL> exec ins_test2('a',8);
BEGIN ins_test2('a',8); END;

*
ERROR at line 1:
ORA-00001: unique constraint (JYANG.TEST2_PK) violated 
ORA-06512: at "JYANG.INS_TEST2", line 9 
ORA-06512: at line 1 


SQL> -- Error message still logged
SQL> select * from msg_tab;

C1                                                                              
--------------------------------------------------------------------------------
1 row inserted                                                                  
ORA-00001: unique constraint (JYANG.TEST2_PK) violated                          

SQL> -- Previously inserted record in test2 still there
SQL> select * from test2;

C1         C2                                                                   
-- ----------                                                                   
b           2                                                                   
c           3                                                                   
e           1                                                                   
K           6                                                                   
P           0                                                                   
G           0                                                                   
ap          9                                                                   
a           8                                                                   
u           7                                                                   

9 rows selected.

SQL> 
SQL> rollback;

Rollback complete.

SQL> -- Rollback does not roll back rows insert by the AUTONOMOUS_TRANSACTION routine.
SQL> select * from msg_tab;

C1                                                                              
--------------------------------------------------------------------------------
1 row inserted                                                                  
ORA-00001: unique constraint (JYANG.TEST2_PK) violated                          

SQL> -- Row in test2 rolled back.
SQL> select * from test2;

C1         C2                                                                   
-- ----------                                                                   
b           2                                                                   
c           3                                                                   
e           1                                                                   
K           6                                                                   
P           0                                                                   
G           0                                                                   
ap          9                                                                   
u           7                                                                   

8 rows selected.

SQL> 
SQL> spool off