Oracle Scheduled Email notification

Small_program_task This table is an email notification to be sent, and once again there is a job that has not received a widget back in the scan fixed time to write it into the table, Send_flag as N, expressed as a notification.
Email_notice_expired This procedure to send correspondence, simple HTML is also written directly in procedure.

/*Email Notification procedure Email_notice_expiredscan Small_program_task, send the Send_flag to N. Written by Milo 20170309*/CREATE OR REPLACE PROCEDUREEmail_notice_expired asv_email_recipient_stringvarchar( +); V_email_html_contentvarchar2(32767); V_expired_minutes Number;  V_last_active_time date; V_stationvarchar( $); V_programvarchar( $); V_timeout Number; v_task_idvarchar2( $); CURSORC_task_table is    SELECTt1.station, t1.program_id, T1.last_active_time, T1.program_timeout, T1.expir Ed_time, T2.mail_for_error, T1.job_no fromSmall_program_task T1 Left Jointk_cust_prog_m T2 onT1.station=T2.custom_noWHERET1.send_flag= 'N';BEGIN  OPENc_task_table; LOOPFETCHc_task_table intov_station, V_program, V_last_active_time, V_timeout, V_expired_minutes,    V_email_recipient_string, v_task_id; EXIT  whenC_task_table%NOTFOUND; --null or empty string    IF(v_email_recipient_string is NULL ORV_email_recipient_string= ' ') Then      CONTINUE; END IF; --dbms_output.put_line (' v_email_recipient_string: ' | | v_email_recipient_string);v_email_html_content:= '' ||V_station|| ',</p><p> This e-mail is to notify you, the following program were 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>'; --Call procedure to send email.Send_mail (p_to=v_email_recipient_string, P_from= '[Email protected]***.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= '[Email protected]***.com', P_password= '***'); --set Send_flag ' Y ' that a email has been sent.    UpdateSmall_program_taskSetSend_flag= 'Y' whereJob_no=v_task_id; Commit; ENDLOOP; CLOSEc_task_table;END;

You can directly test whether the above procedure can be successfully mailed out.

-- Tests procedure email_notice_expired begin   email_notice_expired; End;

After writing the procedure, it is time to set up an Oracle schedule to call this procedure to send a letter.

/*Call the stored procedure Get_expired_program every 5 minutes.written by Milo on 20170309*/beginSys.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= 'every 10 minutes, sweep the call Get_expired_program once .');End;/

The following SQL statement can be used to view the next time the schedule is executed.

-- See the schedule before SELECT *  from WHERE = ' Email_notice_expired_task ';

Oracle Scheduled Email notification