[转]Oracle 存储过程中发送邮件，并支持用户验证、中文标题和内容 --附 修改

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

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

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

CREATE OR REPLACE PROCEDURE send_mail(
     p_recipient VARCHAR2, -- 邮件接收人
     p_subject   VARCHAR2, -- 邮件标题
     p_message   VARCHAR2  -- 邮件正文
)
IS

    --下面四个变量请根据实际邮件服务器进行赋值
    v_mailhost  VARCHAR2(30) := 'smtp.163.com';     --SMTP服务器地址
    v_user      VARCHAR2(30) := 'user'; --登录SMTP服务器的用户名
    v_pass      VARCHAR2(20) := 'pass';          --登录SMTP服务器的密码
    v_sender    VARCHAR2(50)  := '[e-mail]'; --发送都邮箱，一般与 ps_user 对应

    v_conn  UTL_SMTP.connection; --到邮件服务器的连接
    v_msg varchar2(4000);  --邮件内容

BEGIN

    v_conn := UTL_SMTP.open_connection(v_mailhost, 25);
    UTL_SMTP.ehlo(v_conn, v_mailhost); --是用 ehlo() 而不是 helo() 函数
    --否则会报：ORA-29279: SMTP 永久性错误: 503 5.5.2 Send hello first.

    UTL_SMTP.command(v_conn, 'AUTH LOGIN');   -- smtp服务器登录校验
    UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_user))));
    UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_pass))));

    UTL_SMTP.mail(v_conn, '<' || v_sender || '>');     --设置发件人
    UTL_SMTP.rcpt(v_conn, '<' || p_recipient || '>');  --设置收件人

    -- 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行
    v_msg :='Date:'|| TO_CHAR(SYSDATE, 'dd mon yy hh24:mi:ss')
        || UTL_TCP.CRLF || 'From: '|| v_sender || '<' || v_sender || '>'
        || UTL_TCP.CRLF || 'To: '  || p_recipient || '<' || p_recipient || '>'
        || UTL_TCP.CRLF || 'Subject: ' || p_subject
        || UTL_TCP.CRLF || UTL_TCP.CRLF  -- 这前面是报头信息
        || p_message;    -- 这个是邮件正文

    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.quit(v_conn); --关闭连接

EXCEPTION

    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
        DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);

END send_mail;

CREATE OR REPLACE PROCEDURE send_mail( p_recipient VARCHAR2, -- 邮件接收人 p_subject VARCHAR2, -- 邮件标题 p_message VARCHAR2 -- 邮件正文 ) IS --下面四个变量请根据实际邮件服务器进行赋值 v_mailhost VARCHAR2(30) := 'mail.xxx.com'; --SMTP服务器地址 v_user VARCHAR2(30) := 'user'; --登录SMTP服务器的用户名 v_pass VARCHAR2(20) := 'pass'; --登录SMTP服务器的密码 v_sender VARCHAR2(50) := '[e-mail]'; --发送都邮箱，一般与 ps_user 对应 v_conn UTL_SMTP.connection; --到邮件服务器的连接 v_msg varchar2(4000); --邮件内容 BEGIN v_conn := UTL_SMTP.open_connection(v_mailhost, 25); UTL_SMTP.ehlo(v_conn, v_mailhost); --是用 ehlo() 而不是 helo() 函数 --否则会报：ORA-29279: SMTP 永久性错误: 503 5.5.2 Send hello first. UTL_SMTP.command(v_conn, 'AUTH LOGIN'); -- smtp服务器登录校验 UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_user)))); UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_pass)))); UTL_SMTP.mail(v_conn, v_sender); --设置发件人 UTL_SMTP.rcpt(v_conn, p_recipient); --设置收件人 -- 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行 v_msg :='Date:'|| TO_CHAR(SYSDATE, 'dd mon yy hh24:mi:ss') || UTL_TCP.CRLF || 'From: '|| v_sender || '<' || v_sender || '>' || UTL_TCP.CRLF || 'To: ' || p_recipient || '<' || p_recipient || '>' || UTL_TCP.CRLF || 'Subject: ' || p_subject || UTL_TCP.CRLF || UTL_TCP.CRLF -- 这前面是报头信息 || p_message; -- 这个是邮件正文 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.quit(v_conn); --关闭连接 EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack); END send_mail;

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

begin
    send_mail(中文标题','中文内容');
end;

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

send_mail(中文标题','中文内容');

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

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

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.
        2.
        3. 用oracle发送邮件（功能很全） 介绍了附件的发送
        4.
        5.

---

附注：在 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;