使用自治事务记录日志的错误信息

EODA@PROD1> create table error_log
  2    ( ts	 timestamp,
  3  	 err1 clob,
  4  	 err2 clob )
  5  /
EODA@PROD1> 
EODA@PROD1> create or replace
  2    procedure log_error
  3    ( p_err1 in varchar2, p_err2 in varchar2 )
  4    as
  5  	   pragma autonomous_transaction;      --关键点
  6    begin
  7  	   insert into error_log( ts, err1, err2 )
  8  	   values ( systimestamp, p_err1, p_err2 );
  9  	   commit;
 10    end;
 11  /

Procedure created.

EODA@PROD1> 
EODA@PROD1> create table t ( x int check (x>0) );

Table created.

EODA@PROD1> 
EODA@PROD1> create or replace procedure p1( p_n in number )
  2    as
  3    begin
  4  	   -- some code here
  5  	   insert into t (x) values ( p_n );
  6    end;
  7  /

Procedure created.

EODA@PROD1> 
EODA@PROD1> create or replace procedure p2( p_n in number )
  2    as
  3    begin
  4  	   -- code
  5  	   -- code
  6  	   p1(p_n);
  7    end;
  8  /

Procedure created.

EODA@PROD1> 
EODA@PROD1> begin
  2  	   p2( 1 );
  3  	   p2( 2 );
  4  	   p2( -1);
  5    exception
  6  	   when others
  7  	   then
  8  	       log_error( sqlerrm, dbms_utility.format_error_backtrace );
  9  	       RAISE;
 10    end;
 11  /
begin
*
ERROR at line 1:
ORA-02290: check constraint (EODA.SYS_C0018380) violated
ORA-06512: at line 9


EODA@PROD1> 
EODA@PROD1> select * from t;

no rows selected

EODA@PROD1> rollback;

Rollback complete.

EODA@PROD1> select * from error_log;

TS
---------------------------------------------------------------------------
ERR1
--------------------------------------------------------------------------------
ERR2
--------------------------------------------------------------------------------
18-OCT-16 07.49.00.226052 PM
ORA-02290: check constraint (EODA.SYS_C0018380) violated
ORA-06512: at "EODA.P1", line 5
ORA-06512: at "EODA.P2", line 6
ORA-06512: at line 4

--参考来源《Oracle编程艺术深入理解 数据库 体系结构（第三版）》