Oracle 发邮件过程

oracle 版本 11g

 CREATE OR REPLACE PROCEDURE pro_sendmail(sender     IN VARCHAR2 DEFAULT '发件人邮箱',
                                              recipient1 IN VARCHAR2 DEFAULT '收件人邮箱',
                                              recipient2 IN VARCHAR2 DEFAULT '收件人邮箱',
                                              recipient3 IN VARCHAR2 DEFAULT '收件人邮箱',
                                              recipient4 IN VARCHAR2 DEFAULT '收件人邮箱',
                                              subject    IN VARCHAR2 DEFAULT '标题',
                                              message    IN VARCHAR2 DEFAULT '邮件内容',
                                              message01    IN VARCHAR2 DEFAULT '邮件内容') IS
   mailhost VARCHAR2(30) := 'smtp.139.com'; --邮箱服务器地址我这里139邮箱
   --receiver VARCHAR2(30) :='监控管理员';
   mail_passwd varchar(20) :='邮箱密码';
   connter_addr        utl_smtp.connection;
   msg_content      VARCHAR2(1000);
BEGIN
   msg_content := 'Date: ' || to_char(SYSDATE , 'yyyy-mm-dd hh24:mi:ss') ||
          utl_tcp.crlf || 'From: ' || utl_tcp.crlf ||
          'subject: ' || subject || utl_tcp.crlf || 'To:           '>;;' || utl_tcp.crlf || 'Cc:           recipient4 || '>' || utl_tcp.crlf || '' || utl_tcp.crlf ||
          message||message01;
   connter_addr   := utl_smtp.open_connection(mailhost, 25);
   utl_smtp.helo(connter_addr, mailhost);
   utl_smtp.command(connter_addr, 'auth login');
   utl_smtp.command(connter_addr,
                    utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(sender))));
   utl_smtp.command(connter_addr,
                    utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(mail_passwd))));
   utl_smtp.mail(connter_addr, sender);      --发件人
   utl_smtp.rcpt(connter_addr, recipient1);  --收件人
   utl_smtp.rcpt(connter_addr, recipient2);
   utl_smtp.rcpt(connter_addr, recipient3);
   utl_smtp.rcpt(connter_addr, recipient4);

  utl_smtp.open_data(connter_addr);          --打开邮箱连接
   utl_smtp.write_raw_data(connter_addr, utl_raw.cast_to_raw(msg_content));
   utl_smtp.close_data(connter_addr);
   utl_smtp.quit(connter_addr);
   EXCEPTION  
 
    WHEN OTHERS THEN
         DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);  
         DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);   
END pro_sendmail;
 
添加ACL规则
 1）创建访问控制列表acl

 BEGIN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
                                                                      acl          => 'mail server.xml',
                                                                      description  => ' mail server',
                                                                      principal    => 'SYS',--进行操作的数据库用户且用户要大写
                                                                      is_grant     => TRUE,
                                                                      privilege    => 'connect');
END;

2）将ACL与邮件服务器关联（这里我用139邮箱）

 /*新浪的邮箱服务器地址，如果是用qq作为发送邮件，
smtp.139.com  139邮箱
 smtp.163.com  163邮箱
smtp.126.com  126邮箱
smtp.sina.com 新浪邮箱则用qq邮箱服务器地址smtp.qq .com*/

BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
                                                             acl         => 'mail server.xml',
                                                             host        => 'smtp.139.com',
                                                             lower_port  => 25,
                                                             upper_port  => NULL);
  COMMIT;
END;

，可用dba_network_acls查看是否已经生成

 
SELECT host, lower_port, upper_port, acl FROM sys.dba_network_acls;
 

 
 3）为执行的数据库用户授予连接邮件服务器的权限

 

 
BEGIN
  dbms_network_acl_admin.add_privilege(
                                                           acl        => 'mail server.xml',
                                                           principal =>'SYS',--进行操作的数据库用户且用户要大写                                                                        

                                                           is_grant   =>  TRUE,
                                                           privilege  => 'connect');
END;

 采用dba_network_acl_privileges视图查看授予的权限

 
SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
  FROM sys.dba_network_acl_privileges;
 

如果配置规则文件有错误可以执行下面语句重新配置
-- 删除acl配置文件


    
begin
  dbms_network_acl_admin.drop_acl(
    'mail server.xml'
  );
  commit;
 end;

 4.utl_tcp.crlf作用：换行

注意在写邮件标题，发送人，接收人，邮件内容时，各个之间一定要有换行，否则会无法发送或者是邮件内容为空。

 

5.中文乱码问题

    utl_smtp.write_raw_data 用该过程替代utl_smtp.write_data可以解决中文乱码的问题。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29065182/viewspace-1689195/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29065182/viewspace-1689195/