利用ORACLE utl_stmp 包实现 mail发送功能

数据仓库的etl过程会涉及到很多的存储程，因为源数据的多样性，且etl过程大都在夜间无人值守的情况下执行，对于采用手工etl方式的数据仓库环境，需要一种及时快捷的etl过程执行状况通知，可以有2中方式实现

一：在每个存储过程的exception段或者错误信息日志表中增加trigger，通过utl_stmp包发送邮件通知。

二：mail监控方式需要我们主动的去收发邮件，才能知道etl执行状况，有一定的局限性，现在移动通信的高度发达，使我们可以用一种更方便更快捷的方式，利用sms短信通知，为此，需要企业需要花点小钱，增加短信服务平台，在etl的时候，通过存储过程，把每个错误信息发送到短信平台中，然后通过短信平台和移动通信服务商的连接通道发送到手机上，这样，你就可以带着一部手机，走到任何地方，都可以及时知道etl的状况了。

下面简单的介绍一下，如何利用utl_stmp包实现mail发送

实现最简单的mai功能，参见 utl_smtp 包的说明例子，列出需要如下8个步骤

1:l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);

--定义一个stmp连接，指向mail服务器
2: utl_smtp.helo(l_mail_conn, l_mailhost);

--建立连接
3:  utl_smtp.mail(l_mail_conn, p_sender);

--设定发件人地址
  4: utl_smtp.rcpt(l_mail_conn, p_recipient);

--设定邮件人地址
  5: utl_smtp.open_data(l_mail_conn);

--打开预定义的指向smtp的连接通道
  6:utl_smtp.write_data(l_mail_conn, p_message);

--写入邮件正文内容
  7:utl_smtp.close_data(l_mail_conn);

--关闭smtp连接通道
  8: utl_smtp.quit(l_mail_conn);

--退出

上面这些就可以实现一个简单maile功能，不还不实用，不支持中文，不支持邮件密码验证，下面给一个实用的mail 脚本。

CREATE OR REPLACE PROCEDURE send_mail(
                p_sender   In Varchar2,
                p_receiver IN VARCHAR2, 
                p_message   IN VARCHAR2) 
AS 
  mailhost  VARCHAR2(100) := '10.103.41.181';        --smtp mail 服务器 
  mail_conn utl_smtp.connection; 
  PROCEDURE send_header(conn  IN OUT NOCOPY utl_smtp.connection,  
                        name  IN VARCHAR2,  
                        value IN VARCHAR2)
   As  
   BEGIN  
     utl_smtp.write_data(conn,name || ': =?GB2312?B?' ||      --注意使用GB2312才能支持中文 
     utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(value))) || '?='|| utl_tcp.crlf);   
   END;  
  --p_user Varchar2(30) :='hfklj';
  --p_pass Varchar2(20) :='';
BEGIN 
  mail_conn :=utl_smtp.open_connection(mailhost,25); 
  utl_smtp.helo(mail_conn,mailhost); 
 --UTL_SMTP.COMMAND(mail_conn, 'AUTH LOGIN');
 --utl_smtp.command(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_user))));
 --utl_smtp.command(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_pass))));     
  utl_smtp.mail(mail_conn,p_sender);    -- sender 
  utl_smtp.rcpt(mail_conn,p_receiver); -- receiver 
  utl_smtp.open_data(mail_conn); 
  send_header(mail_conn,'From',p_sender);--显示 mail中的发件人
  send_header(mail_conn,'To', p_receiver);  --显示mail中的 收件人 
  --send_header(mail_conn,'Cc', 'hfklj@pec.com.cn');--显示抄送人地址
  --send_header(mail_conn,'Bcc', 'hfklj5@pec.com.cn');--显示秘送人地址
  send_header(mail_conn,'Subject', 'ETL 报错邮件');  --邮件主题 
  UTL_SMTP.write_data(mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13)|| chr(10)); 
  utl_smtp.write_data(mail_conn, 'MIME-Version: 1.0' || chr(13) || chr(10)); 
  utl_smtp.write_data(mail_conn, 'Content-type: text/plain;Charset=UTF8;' || chr(13) || chr(10)); 
  utl_smtp.write_data(mail_conn, 'Content-Transfer-Encoding: base64' || chr(13) || chr(10)); 
  utl_smtp.write_data(mail_conn, chr(13) || chr(10));   
  utl_smtp.write_data(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_message)))); 
  utl_smtp.close_data(mail_conn); 
  utl_smtp.quit(mail_conn); 
EXCEPTION 
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN 
    utl_smtp.quit(mail_conn); 
    raise_application_error(-20000, 
      'Failed tosend mail due to the following error: ' || sqlerrm); 
  WHEN OTHERS THEN 
    raise_application_error(-20001, 
      'The following error has occured: ' || sqlerrm);    
End send_mail;