首页 > 代码库 > Oracle觸發器調用procedure寄信
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 ROWDECLARE 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;/
Oracle觸發器調用procedure寄信
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。