ORACLE 使用UTL_SMTP包发送邮件

转：http://www.blogjava.net/decode360/archive/2009/06/11/282879.html

使用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, '[e-mail]');
UTL_SMTP.RCPT(c, '[e-mail]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <[e-mail]>');
send_header('To', '"Recipient" <[e-mail]>');
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 '[e-mail]',
                                   recipient in varchar2 default '[e-mail]',
                                   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 '[e-mail]',
                                   recipient in varchar2 default '[e-mail]',
                                   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 '[e-mail]',
                                        recipient in varchar2 default '[e-mail]',
                                        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 '[e-mail]',
                                   recipient1 in varchar2 default '[e-mail]',
                                   recipient2 in varchar2 default '[e-mail]',
                                   recipient3 in varchar2 default '[e-mail]',
                                   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、支持发送附件等等。具体操作有需要时再学一下，以下列出地址：

 

 

 

----------------------------

HTML功能传送门： http://www.itpub.net/viewthread.php?tid=633486&extra=&page=1

附件功能传送门： http://lz726.javaeye.com/blog/141456

ASK TOM传送门： http://asktom.oracle.com/pls/asktom/f?p=100:11:93372672528637::::P11_QUESTION_ID:255615160805

Java发邮件传送门： http://www.itpub.net/thread-825426-1-1.html