转: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, '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;
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,
-- 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;
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 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.
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);
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;
'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.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;
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))));
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;
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;
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、支持发送附件等等。具体操作有需要时再学一下,以下列出地址:
----------------------------
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
相关推荐
通过ORACLE的UTL_HTTP工具包发送包含POST参数的请求,包含完成的原创代码、测试说明、相关文档,希望对您的学习有帮助。
oracle plsql 通过utl_http调用 webservice,包括webservice服务端的源码,以及pro的客户端代码。已测试通过。
Oracle学习]ORACLE的UTL_FILE包简析 包UTL_FIle用于读写操作系统的文件,前提是首先创建Directory对象、授权。然后就可以使用UTL_FILE包中提供的类型、过程、函数来读取
oracle utl_file包函数与存储过程的用法
oracle注入utl_http方法.doc
在oracle中utl_file包提供了一些操作文本文件的函数和过程,学习了一下他的基本操作 1.创建directory,并给用户授权 代码如下: –创建directory create or replace directory TESTFILE as ‘/home/oracle/zxx/test’...
该软件包通过utl_smtp编写符合RFC的MIME邮件正文。 它允许简单的文本消息,带有嵌入式图像HTML消息等,以及附件。
描述oracle utl_file包,大家在使用oralce生成文件过程中可以使用。
utl_http.request包探测基本信息.txt
utl_mail oracle scheduler对发邮件的ORACLE包UTL_MAIL以及任务作业详细分析
delphi7做Socket的服务器端,oracle的utl_tcp做Socket的Cliient端.Utl_tcp向server发送信息,并从Server端接收反馈。utl_tcp在PL/SQL Developer 中实现。全部源码。
在oracle9i中设置utl_file_dir参数.txt
附件带截图 博文链接:https://wm02091070.iteye.com/blog/930174
UTL_FILE用法详解
由于Oracle不提供用于实时输出消息的工具, Oracle数据库开发者总是要面临实时监视他们的...我们的做法是把所有必需的过程与函数包装在自定义的包中,然后使用Oracle8i UTL_SMTP包直接地从Oracle数据库中发送电子邮件。
oracle的utl_file工具包。。。
完全可用的oracle发邮件程序,只要编译一下就可以用
IBM 服务器安装Windows2003引导镜像,可以安装Windows2003和Windows2003R2,只是引导镜像,需要刻录光盘,需X58老机器亲测可用,X79机型未使用过
楼主搜了一些资料之后,自己研究用oracle里的UTL_HTTP包成功的访问到了接口,并获取了接口返回。代码是已经在生产环境上跑通并实际在应用。按下面脚本准备即可。脚本支持大文本作为请求入参去post接口。 1.UTL_HTTP...
只使用PLSQL就可以直接实现不同服务器间的文件的FTP功能