使用UTL_SMTP包發(fā)送郵件
?
?
??? 最近想在Oracle中設(shè)置一個(gè)觸發(fā)器，每天執(zhí)行數(shù)據(jù)檢測(cè)腳本之后，如果發(fā)現(xiàn)錯(cuò)誤數(shù)據(jù)就自動(dòng)發(fā)送郵件到我郵箱里，于是研究了一下在Oracle中發(fā)送郵件的方法。據(jù)說(shuō)10g里可以使用UTL_MAIL包來(lái)簡(jiǎn)單得發(fā)送郵件了，但是覺得通用性不高，萬(wàn)一哪天換成9i了就要重寫，于是還是決定用UTL_SMTP包來(lái)做。
?
??? 先簡(jiǎn)單看一下官方文檔上的例子。其實(shí)很簡(jiǎn)單：
?
------------------------------------------
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;
------------------------------------------
?
?
??? 需要說(shuō)明一下的是：必須要按照這個(gè)例子里的順序依次執(zhí)行包中的各個(gè)方法。另外注意用UTL_TCP.CRLF來(lái)作為換行符。理由如下：
?
------------------------------------------
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.
------------------------------------------
?
?
?
??? 別的也沒有什么可說(shuō)的了，自己隨手寫了一個(gè)，因?yàn)槭菃挝坏泥]箱，也不需要驗(yàn)證身份，而且只是發(fā)給自己就可以了。貼一下，很簡(jiǎn)陋不過(guò)夠用了：
?
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;
?
?
------------------------------------------
?
??? 但是這個(gè)腳本存在一個(gè)比較嚴(yán)重的問(wèn)題，就是不能發(fā)送中文，中文發(fā)出去是亂碼的，要解決這個(gè)問(wèn)題，需要把：
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;
?
?
------------------------------------------
?
??? 如果需要使用外網(wǎng)郵箱登陸后發(fā)送郵件，則要加入下面的代碼：
?
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))));
?
??? 具體測(cè)試通過(guò)的代碼如下：
?
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 '我的郵件測(cè)試',
??????????????????????????????????????? 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的行即可，如果需要多個(gè)接受者，則在收件人中列明，并分別用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;
?
?
------------------------------------------
?
??? 基本上先了解這些了，其實(shí)還有很多其他的功能，例如：支持HTML、支持發(fā)送附件等等。具體操作有需要時(shí)再學(xué)一下，以下列出地址：
?
?
?
----------------------------
?
?
?
?