oracle告警邮件,oracle 用UTL_SMTP 发预警邮件

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

上周有个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','

ok

');

打赏

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