使用UTL_SMTP包發送郵件
?
?
??? 最近想在Oracle中設置一個觸發器，每天執行數據檢測腳本之后，如果發現錯誤數據就自動發送郵件到我郵箱里，于是研究了一下在Oracle中發送郵件的方法。據說10g里可以使用UTL_MAIL包來簡單得發送郵件了，但是覺得通用性不高，萬一哪天換成9i了就要重寫，于是還是決定用UTL_SMTP包來做。
?
??? 先簡單看一下官方文檔上的例子。其實很簡單：
?
------------------------------------------
The following example illustrates how UTL_SMTP is used by an application to send e-mail. The application connects to an SMTP server at port 25 and sends a simple text message.
DECLARE
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;
-- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The QUIT call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
------------------------------------------
?
?
??? 需要說明一下的是：必須要按照這個例子里的順序依次執行包中的各個方法。另外注意用UTL_TCP.CRLF來作為換行符。理由如下：
?
------------------------------------------
The calls to OPEN_DATA, WRITE_DATA, WRITE_RAW_DATA and CLOSE_DATA must be made in the right order. A program calls OPEN_DATA to send the DATA command to
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.
?
The application must ensure that the contents of the body parameter conform to the MIME(RFC822) specification. The DATA routine will terminate the message with a <CR><LF>.<CR><LF> sequence (a single period at the beginning of a line), as required by RFC821. It will also translate any sequence of <CR><LF>.<CR><LF> (single period) in the body to <CR><LF>..<CR><LF> (double period). This
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.
WRITE_DATA should be called only after OPEN_CONNECTION, HELO or EHLO, MAIL,and RCPT have been called. The connection to the SMTP server must be open and amail transaction must be active when this routine is called.
Note that there is no function form of WRITE_DATA because the SMTP server does notrespond until the data-terminator is sent during the call to CLOSE_DATA.
?
Text (VARCHAR2) data sent using WRITE_DATA is converted to US7ASCII before it issent. If the text contains multibyte characters, each multibyte character in the text thatcannot be converted to US7ASCII is replaced by a '?' character. If 8BITMIME extensionis negotiated with the SMTP server using the EHLO subprogram, multibyte VARCHAR2data can be sent by first converting the text to RAW using the UTL_RAW package, andthen sending the RAW data using WRITE_RAW_DATA.
------------------------------------------
?
?
?
??? 別的也沒有什么可說的了，自己隨手寫了一個，因為是單位的郵箱，也不需要驗證身份，而且只是發給自己就可以了。貼一下，很簡陋不過夠用了：
?
create or replace procedure P_Mail(sender??? in varchar2 default 'wangxiaoqi@xxxx.com',
?????????????????????????????????? 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.data(c, msg);
? utl_smtp.quit(c);
end P_Mail;
?
?
------------------------------------------
?
??? 但是這個腳本存在一個比較嚴重的問題，就是不能發送中文，中文發出去是亂碼的，要解決這個問題，需要把：
utl_smtp.data
改成用utl_smtp.write_raw_data,
修改為：
?
create or replace procedure P_Mail(sender??? in varchar2 default 'wangxiaoqi@xxxx.com',
?????????????????????????????????? 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, 'auth login');
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))));
?
??? 具體測試通過的代碼如下：
?
create or replace procedure P_Mail_Sina(sender??? in varchar2 default 'decode360@sina.com',
??????????????????????????????????????? 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;
?
?
------------------------------------------
??? 如果需要加入抄送，則在msg中加入'Cc:'作為name的行即可，如果需要多個接受者，則在收件人中列明，并分別用utl_smtp.rcpt連接，如下：
?
?
create or replace procedure P_Mail(sender??? in varchar2 default 'wangxiaoqi@xxxx.com',
?????????????????????????????????? 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;
?
?
------------------------------------------
?
??? 基本上先了解這些了，其實還有很多其他的功能，例如：支持HTML、支持發送附件等等。具體操作有需要時再學一下，以下列出地址：
?
?
?
----------------------------
?
?
?
?