Oracle定時email通知
时间：2021-07-01 10:21:17
帮助过：10人阅读
EMAIL通知procedure email_notice_expired
scan small_program_task表，將send_flag標記為N的寄出去。
written by milo 20170309
*/
CREATE OR REPLACE PROCEDURE email_notice_expired
AS
v_email_recipient_string varchar(
1000);
v_email_html_content varchar2(
32767);
v_expired_minutes number;
v_last_active_time date;
v_station varchar(
200);
v_program varchar(
200);
v_timeout number;
v_task_id varchar2(
36);
CURSOR c_task_table
IS
SELECT t1.station,
t1.program_id,
t1.last_active_time,
t1.program_timeout,
t1.expired_time,
t2.mail_for_error,
t1.job_no
FROM small_program_task t1
left join TK_CUST_PROG_M t2
ON t1.station
= t2.custom_no
WHERE t1.send_flag
= ‘N‘;
BEGIN
OPEN c_task_table;
LOOP
FETCH c_task_table
INTO v_station,
v_program,
v_last_active_time,
v_timeout,
v_expired_minutes,
v_email_recipient_string,
v_task_id;
EXIT WHEN c_task_table
%NOTFOUND;
--null or empty string
IF (v_email_recipient_string
IS NULL OR v_email_recipient_string
= ‘ ‘)
THEN
CONTINUE;
END IF;
--dbms_output.put_line(‘v_email_recipient_string: ‘ || v_email_recipient_string);
v_email_html_content := ‘<html>
<head>
<title></title>
</head>
<body>
<p> Hi ‘ || v_station
|| ‘,</p>
<p> This email is to notify you that the following program was not sent running
status to server. Please check it ASAP.</p>
<p> <Detail></p>
<p> ------------------------------------------------------------------------------</p>
<p> <b>Program Name | Last Active Time | Duration(Minutes)</b></p>
<p> ‘ || v_program
|| ‘ | ‘ ||
to_char(v_last_active_time,
‘dd-mm-yyyy hh24:mi‘)
|| ‘ | ‘ ||
v_expired_minutes || ‘</p>
<p> ------------------------------------------------------------------------------</p>
</body>
</html>‘;
--call procedure to send email.
send_mail(p_to
=> v_email_recipient_string,
p_from => ‘milo@***.com‘,
p_subject => ‘Program Status Notice(‘ || v_program
|| ‘)‘,
p_text_msg => ‘This is a test message.‘,
p_html_msg => v_email_html_content,
p_smtp_host => ‘spam.***.com‘,
p_account => ‘milo@***.com‘,
p_password => ‘***‘);
-- set send_flag ‘Y‘ that an email has been sent.
update small_program_task
set send_flag
= ‘Y‘ where job_no
= v_task_id;
commit;
END LOOP;
CLOSE c_task_table;
END;
可直接先測試以上的procedure是否可以成功寄信出去。
--測試procedure email_notice_expired
begin
email_notice_expired;
end;
寫完了procedure，之後就是設置一個oracle排程去定時call這個procedure寄信就可以了。
/*call the stored procedure get_expired_program every 5 minutes.
written by milo on 20170309
*/
begin
sys.dbms_scheduler.create_job(job_name => ‘PLOEC.EMAIL_NOTICE_EXPIRED_TASK‘,
job_type => ‘STORED_PROCEDURE‘,
job_action => ‘email_notice_expired‘,
start_date => to_date(‘06-03-2017 00:00:00‘, ‘dd-mm-yyyy hh24:mi:ss‘),
repeat_interval => ‘Freq=Minutely;Interval=10‘,
end_date => to_date(‘07-03-2049 00:00:00‘, ‘dd-mm-yyyy hh24:mi:ss‘),
job_class => ‘DBMS_JOB$‘,
enabled => true,
auto_drop => false,
comments => ‘每10分鐘掃呼叫get_expired_program一次‘);
end;
/
可用以下SQL statement查看schedule的下次執行時間等。
--查看當前的schedule
SELECT * FROM dba_scheduler_jobs WHERE job_name = ‘EMAIL_NOTICE_EXPIRED_TASK‘;
Oracle定時email通知
标签：for tor sub stat notice creat status ace pre
