Oracle 存储过程中发送邮件,并支持用户验证、中文标题和内容

在 Oracle 的存储过程执行中,我们可能希望它本身能完成邮件发送执行的结果,特别是在捕获到了异常时。不能总是依赖于调用存储过程的外部程序--调用后,根据出口参数,发送执行结果。这一需求更迫切的表现在非人工参与的 Oracle Job 调用存储过程的情况下。

所幸,Oracle 为我们提供了发送邮件的工具包 UTL_SMTP,它最早出现在 Oracle 8.1.7 版本中。下面是我从网络上搜索相关资料后、综合整理、多处修正、数次调试、排除万难而写出的一个发送邮件的存储过程。可支持需用户验证的邮件服务器,中文标题和中文内容无乱码,只还未支持附件的发送,相信这方面应用较少,需要的话再 Google 一下,且文后参考中有相应的链接。

上面代码在 Oracle 9.2.0(Solaris 平台,数据库字符集是 ZHS16GBK) 中实际运行后通过,在 PL/SQL Developer 的 SQL Window 中用下面代码调用该存储过程:

begin
    send_mail(
'fantasia@sina.com','中文标题','中文内容');
end;

而且在 PL/SQL Developer 的 Command Windows 或是 SQL *Plus 中执行:

send_mail('fantasia@sina.com','中文标题','中文内容');

都能正常发送,成功收到邮件,并且标题和内容的中文显示正常。

网络上直接拿下来的例子,多是没有很好的解决中文文题,有些不能支持邮件服务器的验证,当今时代要找个不需要用户验证的邮件服务器太难了。关键是有个致命问题是,一运行就报类似如下的错误:

ORA-29279: SMTP 永久性错误: 503 5.5.2 Send hello first.

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
38298bd60        45  procedure TCSM.SEND_MAIL
38a4efa40         2  anonymous block

原因是:若邮件服务器需要用户验证时,对邮件服务器打招呼的方式不对,不能写成

UTL_SMTP.helo(v_conn, v_mailhost);

而要写成:

UTL_SMTP.ehlo(v_conn, v_mailhost);

如果你的邮件中只用英文,那么上面代码中的三行:

UTL_SMTP.open_data(v_conn); --打开流
UTL_SMTP.write_raw_data(v_conn, UTL_RAW.cast_to_raw(v_msg));
UTL_SMTP.close_data(v_conn); --关闭流

只需要写成一行就行了,如下:

UTL_SMTP.DATA (mail_conn, v_msg);

也可以把邮件报头和正文信息分开来写入到连接中去,但这样做恐怕对于标题和正文的中文文题会顾此失彼了。

参考:1. 用utl_smtp发送邮件时的汉字解决方法
        2. 实例讲解如何通过Oracle成功发送邮件
        3. 用oracle发送邮件(功能很全) 介绍了附件的发送
        4. Oracle UTL_SMTP
        5. 用telnet发邮件(支持smtp认证)


附注:在 Oracle(8.1.7及以上版本) 中可以用下面语句获得字符串的 base64 编码,如:

select UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('user'))) from dual;
select UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('pass'))) from dual;

本文链接 https://yanbin.blog/oracle-procedure-send-email/, 来自 隔叶黄莺 Yanbin Blog

[版权声明] Creative Commons License 本文采用 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 进行许可。

Subscribe
Notify of
guest

11 Comments
Inline Feedbacks
View all comments