oracle中记录trigger的异常记录

create or replace trigger TIG_BP_MACHINE_DF_BIND
before insert on "BP_MACHINE_DATACOLLECTION_DF" for each row
declare
    V_SFC_NO                VARCHAR2(50);                 --电芯
    V_RESULT                VARCHAR2(50);                 --结果
begin
    IF LENGTH(:NEW.JE_CODE)>10 THEN
        SELECT SFC_NO INTO V_SFC_NO FROM SFC_INFO WHERE PID=:NEW.BATCH_NO;
        SELECT F_PRODBATCH_TO_SFC(V_SFC_NO,:NEW.JE_CODE,:NEW.CREATE_DATE) INTO V_RESULT FROM DUAL;
        IF V_RESULT!='成功' THEN
           P_WriteLog(inserting, updating, '0', V_RESULT||:NEW.BATCH_NO);
        END IF;
    END IF;
exception when others then
    P_WriteLog(inserting, updating, sqlcode, sqlerrm);
end;

使用 存储过程 P_WriteLog 记录错误信息

 

P_WriteLog 存储过程：

CREATE OR REPLACE Procedure P_WriteLog(
  p_Inserting      In Boolean         ,
  p_updating       In Boolean         ,
  p_sqlcode        In Number          ,
  p_sqlerrm        In Varchar2)
As
Begin
  if p_Inserting then
    insert into s_error_log (errcode,errmsg,host,errdate,operation)
         values(p_sqlcode,substr(p_sqlerrm,1,100),userenv('terminal'),sysdate,'inserting');
  else
     if p_updating then
         insert into s_error_log (errcode,errmsg,host,errdate,operation)
         values(p_sqlcode,substr(p_sqlerrm,1,100),userenv('terminal'),sysdate,'updating');
     else
         insert into s_error_log (errcode,errmsg,host,errdate,operation)
         values(p_sqlcode,substr(p_sqlerrm,1,100),userenv('terminal'),sysdate,'deleting');
     end if;
  end if;
End;

 

s_error_log 表：

ERRCODE	NUMBER	错误代码
ERRMSG	VARCHAR2(200)	错误信息
HOST	VARCHAR2(16)	主机
ERRDATE	DATE	创建日期
OPERATION	VARCHAR2(20)	操作