ANBOB™

对于每个数据库不同级别的错误可以做不同的处理，如短信、邮件等

上周有个job 运行失败，也是最近太忙过了两天才发现，后加一job上班前自动运行，检查job运行历史记录，当有错误时发送email通知我。

procedure code:下面如邮箱地址，用户名，密码根据自己改一下，（红色字体问题），内容支持html标签，多收件人

CREATE OR REPLACE PROCEDURE  send_mail (p_recipient    VARCHAR2,   --Recipient
 p_subject      VARCHAR2,    -- mail title
 p_message      VARCHAR2     -- meail content
 )
IS

 v_mailhost   VARCHAR2(30) := 'smtp.mail.com';    --smtp mail server address
 v_user  VARCHAR2(100) := 'name@mail.com';  --email account
 v_pass       VARCHAR2 (20) := '123456';                      -- email account password
 v_sender     VARCHAR2 (100) := 'name@mail.com'; --general, same as  ps_user

 v_conn       UTL_SMTP.connection;
 v_msg        VARCHAR2 (4000);                                 --emial content
 l_to varchar2(4000);
 n binary_integer;
 l_tmp varchar2(4000);
BEGIN
 --make connection to smtp
 v_conn := UTL_SMTP.open_connection (v_mailhost, 25);
 UTL_SMTP.ehlo (v_conn, v_mailhost);               --say hello to server
 UTL_SMTP.command (v_conn, 'AUTH LOGIN');                     -- smtp server auth
 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, v_sender);                                  

--Recipient list e.g. a@anbob.com,b@anbob.com
 l_to := p_recipient || ',';
 loop
 exit when l_to is null;
 n := instr( l_to, ',' );
 l_tmp := substr( l_to, 1, n-1 );
 l_to := substr( l_to, n+1 );
 UTL_SMTP.rcpt (v_conn, l_tmp);
 end loop;

 v_msg :=
 'Date:'
 || TO_CHAR (SYSDATE, 'dd mon yy hh24:mi:ss')
 || UTL_TCP.CRLF
 || 'From: '
 || v_sender
 || '<'
 || v_sender
 || '>'
 || UTL_TCP.CRLF
 || 'To: '
 || p_recipient
 || '<'
 || p_recipient
 || '>'
 || UTL_TCP.CRLF
 || 'Subject: '
 || p_subject
 || UTL_TCP.CRLF
 || 'Content-Type: text/html'
 || UTL_TCP.CRLF
 || p_message;
 -- start the mai body
 UTL_SMTP.open_data (v_conn);
 UTL_SMTP.write_raw_data (v_conn, UTL_RAW.cast_to_raw (v_msg));
 UTL_SMTP.close_data (v_conn);
 UTL_SMTP.quit (v_conn);
 exception
 when utl_smtp.transient_error or utl_smtp.permanent_error then
 utl_smtp.quit(v_conn);
 raise;
 when others then
 raise;
END send_mail;

测试一下自己的地址

exec send_mail('admin@anbob.com,dba@anbob.com','warning:test','<h2>ok</h2>');

打赏

微信扫一扫，打赏作者吧～