Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to realize the alarm function by sending email automatically in oracle database

2025-01-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

This article mainly introduces "oracle database automatic email how to achieve alarm function". In daily operation, I believe that many people have doubts about how to achieve alarm function in oracle database automatic email. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubt of "how to achieve alarm function in oracle database automatic email". Next, please follow the editor to study!

We had a need to check whether the job in oracle was executed successfully every day, but it was troublesome to check it manually. We wrote a stored procedure and sent emails regularly.

A total of two stored procedures are involved, the first of which is to set who to email and what to send.

CREATE OR REPLACE PROCEDURE CHECK_JOBS_GLAS / * check whether the scheduled task (jobs) is executed successfully and send an email. By guoliang June 28, 2016 * / V_NUMBER VARCHAR2 (10); BEGIN-check the dba_ JBs table to see if there is an execution error job SELECT COUNT (1) INTO V_NUMBER FROM USER_JOBS WHERE SCHEMA_USER = 'Mxxxxx' AND failures' IF V_NUMBER ='0' THEN send_mail_gl ('guoliang@xxxx.cn',' database timed task executed successfully', 'Hello, database timed task executed successfully, please know'); ELSE send_mail_gl ('guoliang@xxxx.cn',', database timed task failed', 'Hello, database timed task failed, please check'); END IF DBMS_OUTPUT.put_line (V_NUMBER); END

The second is the send_mail_gl program mentioned above:

CREATE OR REPLACE PROCEDURE SEND_MAIL_GL (p_recipient VARCHAR2,-- email recipient p_subject VARCHAR2 -- email title p_message VARCHAR2-- email body) IS-- Please assign the following four variables according to the actual mail server v_mailhost VARCHAR2 (30): = 'smtp.exmail.qq.com' -- SMTP server address v_user VARCHAR2 (30): = 'devops@xxx.cn';-- user name of login to SMTP server v_pass VARCHAR2 (20): =' xxxx';-- password of login to SMTP server v_sender VARCHAR2 (50): = 'devops@xxx.cn';-- sender's mailbox, usually corresponding to ps_user. -- connection to mail server v_msg VARCHAR2 (4000);-- email content BEGIN v_conn: = UTL_SMTP.open_connection (v_mailhost, 25); UTL_SMTP.ehlo (v_conn, v_mailhost);-- use ehlo () instead of helo () function-- otherwise it will report: ORA-29279: SMTP permanent error UTL_SMTP.command (v_conn, 'AUTH LOGIN') -- smtp server login verification UTL_SMTP.command (v_conn, UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (v_user); UTL_SMTP.command (v_conn, UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (v_pass) UTL_SMTP.mail (v_conn,'');-- set the sender UTL_SMTP.rcpt (v_conn,'') -- set the recipient-- create the content of the message to be sent. Note that there is a blank line between the header information and the body of the message v_msg: = 'Date:' | | TO_CHAR (SYSDATE) 'yyyy mm dd hh34:mi:ss') | | UTL_TCP.CRLF | |' From:'| | v_sender | |'| UTL_TCP.CRLF | |'To:'| p_recipient | |''| UTL_TCP.CRLF | | 'Subject:' | | p_subject | | UTL_TCP.CRLF | | UTL_TCP.CRLF-the header information is in front of it | | p_message | -- this is the email body UTL_SMTP.open_data (v_conn);-- Open stream UTL_SMTP.write_raw_data (v_conn, UTL_RAW.cast_to_raw (v_msg));-- write title and content in Chinese UTL_SMTP.close_data (v_conn);-- close stream UTL_SMTP.quit (v_conn). -- close the connection EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack); END SEND_MAIL_GL; at this point, the study on "how to automatically send e-mail to the oracle database to achieve alarm function" is over. I hope to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report