Oracle觸發器調用procedure寄信

最近寫了一直Web Service給很多不同站的客戶端呼叫，并直接寄信通知程式中的異常。

直接在oracle中設置某張表的trigger（after insert），當有新的異常資料寫入時候，寄給相關站別的管理員。

 

/* Written by milo on 2017-03-09
*觸發器發送email
*/
CREATE OR REPLACE TRIGGER small_program_exception_afin
  AFTER INSERT ON small_program_exception
  FOR EACH ROW
DECLARE
  l_html         VARCHAR2(32767);
  v_station_name varchar(300);
  v_email_string varchar2(1000);
  v_program_name varchar2(300);
  v_station_code varchar(300);
BEGIN
  /*
  dbms_output.put_line('new.station: ' || :new.station);
  dbms_output.put_line('new.program_id: ' || :new.program_id);
  dbms_output.put_line('new.occurred_time: ' ||
                       to_char(:new.occurred_time, 'yyyy-mm-dd hh24:mm:ss'));
  dbms_output.put_line('new.program_id: ' ||
                       substr(:new.exception_message, 1, 32767));
  */

  BEGIN
    --獲取程式名稱、接收人email以及站名稱
    select s.stn_name, m.mail_for_error, p.name,s.stn_code
      into v_station_name, v_email_string, v_program_name,v_station_code
      from tk_cust_prog_m m, tk_cust_prog_d d, station s, tk_programe p
     where m.custom_no = d.custom_no
       and m.custom_no = s.stn_code
       and d.prog_id = p.id
       and d.prog_id = :new.program_id
       and d.custom_no = :new.station;
  exception
    when no_data_found then
      raise_application_error(-20001, 'No data found.');
      --dbms_output.put_line('no_data_found');
      --dbms_output.put_line('v_email_string: ' || v_email_string);
      if (v_email_string is null or v_email_string = ' ') then
        raise_application_error(-20001,
                                'Errors email recipient should not be empty, please type the recipient''s email address on the web-function');
      end if;
  END;

  l_html := '<html>
<head>
    <title></title>
</head>
<body>
<p> Dear ' || v_station_code || '</p>
<p> This email is to notify you that an unexpected error occurred in the program. Please check it ASAP.</p>
<p> Exception Message</p>
<p> ------------------------------------------------------------------------------</p>
<p> <b>' || v_station_name || '</b></p>
<p> <b>' || v_program_name || '</b></p>
<p> Posted on ' ||
            to_char(:new.occurred_time, 'mm-dd-yyyy hh24:mm:ss') || '</p>
<p> ' || substr(:new.exception_message, 1, 32767) || '</p>
<p> ------------------------------------------------------------------------------</p>
</body>
</html>';

  send_mail(p_to        => v_email_string,
            p_from      => 'milo@***.com', -- ***@oecgroup.com
            p_subject   => 'Program Occurred Errors Notice',
            p_text_msg  => '',
            p_html_msg  => l_html,
            p_smtp_host => 'spam.***.com', 
            p_account   => 'milo@***.com', 
            p_password  => '***'); 
END;
/

 

posted @ 2017-03-11 14:54  lzone6  阅读(290)  评论(0)  编辑  收藏  举报