Oracle觸發器調用procedure寄信
时间：2021-07-01 10:21:17
帮助过：13人阅读
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;
/
Oracle觸發器調用procedure寄信
标签：end address begin 不同 pre sub htm app output
