怎样利用Oracle自动发mail

[kingpeak]

比如：某个值高于警戒值，则自动发mail, 利用java,Lotus notes

[wxdsun]

可以调用封装好了的存储过程发mail，当然原理是通过java发送的，你可以去这里下载例子，
http://otn.oracle.com/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html
很不错的，我一直再用，很方便的！

[beckhambobo]

PROCEDURE sendmail1 (
sender IN VARCHAR2,
recipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2,
send IN VARCHAR2
)
IS
mailhost VARCHAR2(100) := '10.*.*.*';
mail_conn utl_smtp.connection;

crlf VARCHAR2(2) := CHR (13) || CHR (10);
mesg VARCHAR2(3000);
BEGIN
--htp.p('Contacting SMTP server: '||mailhost||'...');
mail_conn := utl_smtp.open_connection (mailhost, 25);
--htp.p('Successfully connected to SMTP server: '||mailhost);
--mesg := 'Date: ' ||
-- TO_CHAR (SYSDATE,'dd Mon yy hh24:mi:ss') ||
--TO_CHAR (SYSDATE,'yy mm dd hh24:mi:ss') ||
mesg:= 'Date: ' || to_char(sysdate,'YYYY-MON-DD HH24:MI:SS','NLS_DATE_LANGUAGE=AMERICAN') || crlf ||
--crlf ||
'From: <' ||
sender ||
'>' ||
crlf ||
'Subject: ' ||
subject ||
crlf ||
'To: ' ||
recipient ||
crlf ||
'' ||crlf ||
message;
utl_smtp.helo (mail_conn, mailhost);
utl_smtp.mail (mail_conn, sender);
utl_smtp.rcpt (mail_conn, recipient);
utl_smtp.data (mail_conn, mesg);
utl_smtp.quit (mail_conn);
END SENDMAIL1;


CREATE OR REPLACE PROCEDURE SEND_EMAIL( p_sender IN VARCHAR2, --发送者
p_recipient IN VARCHAR2, --接收者
p_title IN VARCHAR2, --主题
p_message IN VARCHAR2, --内容
p_priority IN NUMBER, p_server IN vARCHAR2, --接收邮件的服务器
p_port IN VARCHAR2 --端口号
) AS
l_mail_conn UTL_SMTP.CONNECTION;
BEGIN
l_mail_conn := UTL_SMTP.OPEN_CONNECTION(p_server, p_port); --默认端口号

UTL_SMTP.HELO(l_mail_conn, p_server);
UTL_SMTP.MAIL(l_mail_conn, p_sender);
UTL_SMTP.RCPT(l_mail_conn, p_recipient);

UTL_SMTP.OPEN_DATA(l_mail_conn);
--Email头

/*
UTL_SMTP.WRITE_DATA(l_mail_conn,'From:' || p_sender || UTL_TCP.CRLF); --发送者
UTL_SMTP.WRITE_DATA(l_mail_conn,'To:' || p_sender || UTL_TCP.CRLF); --接收者
UTL_SMTP.WRITE_DATA(l_mail_conn,'Cc:' || p_sender || UTL_TCP.CRLF); --抄送者
UTL_SMTP.WRITE_DATA(l_mail_conn,'Subject:' || p_title || UTL_TCP.CRLF); --主题
*/
UTL_SMTP.WRITE_DATA(l_mail_conn,utl_raw.cast_to_raw(convert('From:' || p_recipient || utl_tcp.CRLF,'gb2312')));

UTL_SMTP.WRITE_DATA(l_mail_conn,utl_raw.cast_to_raw(convert('To:' || p_sender || utl_tcp.CRLF,'gb2312')));

UTL_SMTP.WRITE_DATA(l_mail_conn,utl_raw.cast_to_raw(convert('Cc: cccc' || utl_tcp.CRLF,'gb2312')));

UTL_SMTP.WRITE_DATA(l_mail_conn,utl_raw.cast_to_raw(convert('Subject:' || p_title || utl_tcp.CRLF,'gb2312')));

UTL_SMTP.WRITE_DATA(l_mail_conn,'X-Priority: ' || p_priority || UTL_TCP.CRLF); --优先级

--内容
-- UTL_SMTP.WRITE_DATA(l_mail_conn, p_message); --内容

UTL_SMTP.WRITE_DATA(l_mail_conn, utl_raw.cast_to_raw(convert(p_message,'gb2312')));

UTL_SMTP.CLOSE_DATA(l_mail_conn);
UTL_SMTP.QUIT(l_mail_conn);
EXCEPTION
WHEN OTHERS THEN
UTL_SMTP.QUIT(l_mail_conn);
END;

[dinya2003]

我执行的时候出现的错误如下.是什么造成的?是不是系统中的mail服务没有运行呢?
*
ERROR 位于第1行：
ORA-20001: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 83
ORA-06512: at "SYS.UTL_SMTP", line 356
ORA-06512: at "APPS.SEND_MAIL", line 31
ORA-06512: at line 1

[hepp]

指定smtp服务器的时候用ip

[dinya2003]

不行啊,好象还是出现这个错误

[dinya2003]

beckham可以解释一下吗?

[haifeng1012]

up,studying...