Oracle Scheduled Email notification

Source: Internet
Author: User


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.