最近想在Oracle中设置一个触发器,每天执行数据检测脚本之后,如果发现错误数据就自动发送邮件到我邮箱里,于是研究了一下在Oracle中发送邮件的方法。据说10g里可以使用UTL_MAIL包来简单得发送邮件了,但是觉得通用性不高,万一哪天换成9i了就要重写,于是还是决定用UTL_SMTP包来做。
c UTL_SMTP.CONNECTION;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;
BEGIN
c := UTL_SMTP.OPEN_CONNECTION('smtp-server.acme.com');
UTL_SMTP.HELO(c, 'foo.com');
UTL_SMTP.MAIL(c, 'sender@foo.com');
UTL_SMTP.RCPT(c, 'recipient@foo.com');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <sender@foo.com>');
send_header('To', '"Recipient" <recipient@foo.com>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL;
-- a connection to the server. The QUIT call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,
END;
the SMTP server. After that, it can call WRITE_DATA or WRITE_RAW_DATA repeatedly to send the actual data. The data is terminated by calling CLOSE_DATA. After OPEN_DATA is called, the only subprograms that can be called are WRITE_DATA, WRITE_RAW_DATA, or CLOSE_DATA. A call to other APIs will result in an INVALID_OPERATION exception being raised.
conversion provides the transparency as described in Section 4.5.2 of RFC821.
Notice that this conversion is not bullet-proof. Consider this code fragment:
UTL_SMTP.WRITE_DATA('some message.' || chr(13) || chr(10));
UTL_SMTP.WRITE_DATA('.' || chr(13) || chr(10));
Since the sequence <CR><LF>.<CR><LF> is split between two calls to WRITE_DATA,the implementation of WRITE_DATA will not detect the presence of the data-terminator sequence, and therefore, will not perform the translation. It will be the responsibility ofthe user to handle such a situation, or it may result in premature termination of themessage data.
------------------------------------------
recipient in varchar2 default 'wangxiaoqi@xxxx.com',
subject in varchar2 default 'The Wrong Data Noticement',
message in varchar2) is
mailhost varchar2(30) := '10.27.9.24';
c utl_smtp.connection;
msg varchar2(1000);
'From: <' || sender || '>' || UTL_TCP.CRLF ||
'subject: ' || subject || UTL_TCP.CRLF ||
'To: <' || recipient || '>' || UTL_TCP.CRLF ||
'' || UTL_TCP.CRLF || message;
utl_smtp.mail(c, sender);
utl_smtp.rcpt(c, recipient);
utl_smtp.data(c, msg);
utl_smtp.quit(c);
end P_Mail;
recipient in varchar2 default 'wangxiaoqi@xxxx.com',
subject in varchar2 default 'The Wrong Data Noticement',
message in varchar2) is
mailhost varchar2(30) := '10.27.9.24';
c utl_smtp.connection;
msg varchar2(1000);
begin
msg := 'Date: ' || to_char(sysdate - 1, 'dd mon yy hh24:mi:ss') || UTL_TCP.CRLF ||
'From: <' || sender || '>' || UTL_TCP.CRLF ||
'subject: ' || subject || UTL_TCP.CRLF ||
'To: <' || recipient || '>' || UTL_TCP.CRLF ||
'' || UTL_TCP.CRLF || message;
c := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, sender);
utl_smtp.rcpt(c, recipient);
utl_smtp.open_data(c);
utl_smtp.write_raw_data(c, utl_raw.cast_to_raw(msg));
utl_smtp.close_data(c);
utl_smtp.quit(c);
end P_Mail;
utl_smtp.command(c, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(username))));
utl_smtp.command(c, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(password))));
recipient in varchar2 default 'decode360@gmail.com',
subject in varchar2 default '我的邮件测试',
message in varchar2) is
mailhost varchar2(30) := '202.108.3.190'; --ping smpt.sina.com
c utl_smtp.connection;
msg varchar2(1000);
begin
msg := 'Date: ' || to_char(sysdate - 1, 'dd mon yy hh24:mi:ss') || UTL_TCP.CRLF ||
'From: <' || sender || '>' || UTL_TCP.CRLF ||
'subject: ' || subject || UTL_TCP.CRLF ||
'To: <' || recipient || '>' || UTL_TCP.CRLF ||
'' || UTL_TCP.CRLF || message;
c := utl_smtp.open_connection(mailhost, 25);
utl_smtp.command(c, 'auth login');
utl_smtp.command(c, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('decode360'))));
utl_smtp.command(c, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('82654643'))));
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, sender);
utl_smtp.rcpt(c, recipient);
utl_smtp.open_data(c);
utl_smtp.write_raw_data(c, utl_raw.cast_to_raw(msg));
utl_smtp.close_data(c);
utl_smtp.quit(c);
end P_Mail_Sina;
recipient1 in varchar2 default 'wangxiaoqi@xxxx.com',
recipient2 in varchar2 default 'test01@xxxx.com',
recipient3 in varchar2 default 'test02@xxxx.com',
subject in varchar2 default 'The Wrong Data Noticement',
message in varchar2) is
mailhost varchar2(30) := '10.27.9.24';
c utl_smtp.connection;
msg varchar2(1000);
begin
msg := 'Date: ' || to_char(sysdate - 1, 'dd mon yy hh24:mi:ss') || UTL_TCP.CRLF ||
'From: <' || sender || '>' || UTL_TCP.CRLF ||
'subject: ' || subject || UTL_TCP.CRLF ||
'To: <' || recipient1 || '>;<'||recipient2||'>'|| UTL_TCP.CRLF ||
'Cc: <' || recipient3 || '>' || UTL_TCP.CRLF ||
'' || UTL_TCP.CRLF || message;
c := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, sender);
utl_smtp.rcpt(c, recipient1);
utl_smtp.rcpt(c, recipient2);
utl_smtp.rcpt(c, recipient3);
utl_smtp.open_data(c);
utl_smtp.write_raw_data(c, utl_raw.cast_to_raw(msg));
utl_smtp.close_data(c);
utl_smtp.quit(c);
end P_Mail;
------------------------------------------