Monday, June 24, 2013

Trigger to Log and Email critical errors


CREATE OR REPLACE TRIGGER SYS.MAIL_ERROR1
  AFTER SERVERERROR ON DATABASE
DECLARE
  V_CLIENT VARCHAR2(100) := SYS_CONTEXT ('USERENV', 'TERMINAL');
  V_IP VARCHAR2(100) := SYS_CONTEXT ('USERENV', 'IP_ADDRESS');
  V_MODULE VARCHAR2(100) := SYS_CONTEXT('USERENV', 'MODULE');
  V_ERROR_STACK varchar2(6000) :=DBMS_UTILITY.FORMAT_ERROR_STACK;
  V_ERROR_ID  varchar2(100)  := SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK,0,9);
  v_From      VARCHAR2(80) := 'user1@HOMEtest.loc';
  v_Recipient VARCHAR2(80) := 'user1@HOMEtest.loc';
  --v_Recipient VARCHAR2(80) := 'user2@HOME.loc';
  v_Subject   VARCHAR2(80) := 'test from HOMEtest.loc';
  v_Mail_Host VARCHAR2(30) := 'mail.HOMEtest.loc';
  v_Mail_Conn utl_smtp.Connection;
  msg clob :='';
  crlf        VARCHAR2(2)  := chr(13)||chr(10);
  BEGIN
  IF
  V_MODULE ='OMS'
  --DBMS_UTILITY.FORMAT_ERROR_STACK  like 'ORA-01017%'
  THEN
  NULL;
  ELSE
  IF V_ERROR_ID IN ('ORA-06550','ORA-28009')
  THEN
  msg := msg||' Dear';
 v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Data(v_Mail_Conn,
   'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
   'From: '   || v_From || crlf ||
   'Subject: '|| v_Subject || crlf ||
   'To: '     || v_Recipient || crlf ||
   crlf ||
   'ERROR CODE' || crlf || V_ERROR_ID || -- Message body
   ' ' ||'' || crlf || '' || crlf || 'ERROR DETAILS ' || crlf || V_ERROR_STACK
);
utl_smtp.Quit(v_mail_conn);
INSERT INTO HOMElog.error_log
    (TIMESTAMP, USERNAME, INSTANCE, DATABASE_NAME, ERROR_STACK, CLIENT, IP_ADDRESS, MODULE, ERROR_ID)
    VALUES
      (SYSDATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.DATABASE_NAME,DBMS_UTILITY.FORMAT_ERROR_STACK,V_CLIENT,V_IP,V_MODULE,SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK,0,9)
              );
              commit;
              ELSE
                            INSERT INTO HOMElog.error_log
    (TIMESTAMP, USERNAME, INSTANCE, DATABASE_NAME, ERROR_STACK, CLIENT, IP_ADDRESS, MODULE, ERROR_ID)
    VALUES
      (SYSDATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.DATABASE_NAME,DBMS_UTILITY.FORMAT_ERROR_STACK,V_CLIENT,V_IP,V_MODULE,SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK,0,9)
              );
              commit;            
--end if;
end if;
END IF;
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
   raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
  --Mail sending ends here              
  END MAIL_ERROR1;
/

No comments:

Post a Comment