曾经写过一个利用oracle smtp包发mail的procedure，但当时没有增加用户的密码验证功能，这次正好公司的smtp服务器取消的匿名邮件发送，就此机会，完善了该procedure。

CREATE OR REPLACE PROCEDURE send_mail(
                p_sender   In Varchar2  default  'xxx@xxx.com.cn',
                p_receiver IN Varchar2 default 'xxx@xxx.com.cn', 
                p_message   IN Varchar2  Default 'ceshi') 
AS 
  mailhost  VARCHAR2(100) := '10.x.x.x;        --smtp mail 服务器 
  mail_conn utl_smtp.connection; 
  p_user Varchar2(30) :='systemuser@xxx.com.cn';--定义验证的mail帐号
  p_pass Varchar2(20) :='sysuser';--定义改帐号的密码
  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;  

BEGIN 
  mail_conn :=utl_smtp.open_connection(mailhost,25); 
  --utl_smtp.helo(mail_conn,mailhost);  --注释掉此语句
 UTL_SMTP.COMMAND(mail_conn, 'AUTH LOGIN'); --用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', 'xxx@xxx.com.cn');--显示抄送人地址
  --send_header(mail_conn,'Bcc', 'xxx@xxx.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;