Oracle发送邮件，支持HTML，多收件人，多附件

Oracle发邮件，权限问题

- 创建 ACL

BEGIN

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'email_server_permissions.xml',

description => 'Enables network permissions for the e-mail server',

principal => 'LJZ',

is_grant => TRUE,

privilege => 'connect');

END;

-- 与邮件服务关联

BEGIN

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'email_server_permissions.xml',

host => 'smtp.163.com',

lower_port => 25,

upper_port => NULL);

END;

-- 这样 email_user 用户帐户创建的存储过程便可以向此邮件服务器发送邮件

         

--删除

BEGIN

DBMS_NETWORK_ACL_ADMIN.drop_acl(acl => 'email_server_permissions.xml');

COMMIT;

END;

--查询

SELECT host, lower_port, upper_port, acl FROM dba_network_acls;

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 dba_network_acl_privileges;

存储过程

 

create or replace procedure p_send_mail(p_from            in varchar2,
                                         p_to              in varchar2,
                                         p_subject         in varchar2,
                                         p_text            in clob default null,
                                         p_html            in clob default null,
                                         p_attachment_path varchar2,
                                         p_smtp_hostname   in varchar2,
                                         p_smtp_portnum    in varchar2 default 25,
                                         p_need_valid      varchar2 default 'Y',
                                         p_user_name       varchar2,
                                         p_user_pwd        varchar2) is
  /*
  作用：用oracle发送邮件
  主要功能：1、支持多收件人。
            2、支持中文
            3、支持抄送人
            4、支持大于32K的附件
            5、支持多行正文
            6、支持多附件
            7、支持HTML邮件
  作者：HH
  参数说明：
            p_from            发件人
            p_to              收件人，多收件人用逗号或分号分隔
            p_subject         主题
            p_text            文本内容
            p_html            HTML内容
            p_attachment_path 附件地址（绝对路径）,多附件用逗号或分号分隔
            p_smtp_hostname   邮件服务器地址 例：smtp.163.com
            p_smtp_portnum    端口号，默认25
            p_need_valid      是否需要用户名密码验证，默认需要（'Y'）
            p_user_name       用户名
            p_user_pwd        密码
  */
  l_crlf                   varchar2(2) := utl_tcp.crlf;
  l_write_encode           varchar2(20) := 'zhs16gbk';
  l_attachment_encode      varchar2(10) := 'base64';
  l_attachment_mime_type   varchar2(50) := 'application/octet-stream';
  l_attachment_disposition varchar2(10) := 'attachment';
  l_boundary_mail          varchar2(255) default '#---hh***hh-mail---#';
  l_boundary_content       varchar2(255) default '#---hh***hh-content---#';
  l_first_boundary constant varchar2(256) := '--' || l_boundary_mail ||
                                             l_crlf;
  l_last_boundary  constant varchar2(256) := '--' || l_boundary_mail || '--' ||
                                             l_crlf;
  l_connection          utl_smtp.connection;
  l_body_html           clob := empty_clob; --HTML邮件内容
  l_offset              number;
  l_ammount             number;
  l_temp                varchar2(32767) default null;
  l_file_handle         utl_file.file_type;
  l_line                varchar2(1000);
  l_mesg                varchar2(32767);
  l_filepos             pls_integer := 1;
  l_fil                 bfile;
  l_file_len            number;
  l_modulo              number;
  l_pieces              number;
  l_amt                 number := 8580;
  l_chunks              number;
  l_buf                 raw(32767);
  l_data                raw(32767);
  l_max_line_width      number := 54;
  l_directory_base_name varchar2(100) := 'DIR_FOR_SEND_MAIL';
  l_receivers           varchar2(32767);
  l_count               number;
  type address_list is table of varchar2(100) index by binary_integer;
  my_address_list address_list;
  type acct_list is table of varchar2(100) index by binary_integer;
  my_acct_list acct_list;
  --分割邮件地址或者附件地址
  procedure sub_splite_str(p_str varchar2, p_splite_flag int default 1) is
    l_addr varchar2(254) := '';
    l_len  int;
    l_str  varchar2(4000);
    j      int := 0; --表示邮件地址或者附件的个数
  begin
    /*处理接收邮件地址列表，包括去空格、将;转换为,等*/
    l_str := trim(rtrim(replace(replace(p_str, ';', ','), ' ', ''), ','));
    l_len := length(l_str);
    for i in 1 .. l_len loop
      if substr(l_str, i, 1) <> ',' then
        l_addr := l_addr || substr(l_str, i, 1);
      else
        j := j + 1;
        if p_splite_flag = 1 then
          --表示处理邮件地址
          --前后需要加上'<>'，否则很多邮箱将不能发送邮件
          l_addr := '<' || l_addr || '>';
          --调用邮件发送过程
          my_address_list(j) := l_addr;
        elsif p_splite_flag = 2 then
          --表示处理附件名称
          my_acct_list(j) := l_addr;
        end if;
        l_addr := '';
      end if;
      if i = l_len then
        j := j + 1;
        if p_splite_flag = 1 then
          --调用邮件发送过程
          l_addr := '<' || l_addr || '>';
          my_address_list(j) := l_addr;
        elsif p_splite_flag = 2 then
          my_acct_list(j) := l_addr;
        end if;
      end if;
    end loop;
  end;

  --删除directory
  procedure sub_drop_directory(p_directory_name varchar2) is
  begin
    select count(1)
      into l_count
      from dba_directories
     where directory_name = upper(p_directory_name);
    if l_count > 0 then
      execute immediate 'drop directory ' || p_directory_name;
    end if;
  exception
    when others then
      --null;
      raise;
  end;

  --创建directory
  procedure sub_create_directory(p_directory_name varchar2, p_dir varchar2) is
  begin
    execute immediate 'create directory ' || p_directory_name || ' as ''' ||
                      p_dir || '''';
    execute immediate 'grant read,write on directory ' || p_directory_name ||
                      ' to public';
  exception
    when others then
      raise;
  end;

  --返回附件源文件所在目录或者名称
  function sub_get_file(p_file varchar2, p_get int) return varchar2 is
    --p_get=1 表示返回目录
    --p_get=2 表示返回文件名
    l_file varchar2(1000);
  begin
    if instr(p_file, '') > 0 then
      if p_get = 1 then
        l_file := substr(p_file, 1, instr(p_file, '', -1) - 1) || '';
      elsif p_get = 2 then
        l_file := substr(p_file,
                         - (length(p_file) - instr(p_file, '', -1)));
      end if;
    elsif instr(p_file, '/') > 0 then
      if p_get = 1 then
        l_file := substr(p_file, 1, instr(p_file, '/', -1) - 1);
      elsif p_get = 2 then
        l_file := substr(p_file,
                         - (length(p_file) - instr(p_file, '/', -1)));
      end if;
    end if;
    return l_file;
  end;

  --发送附件
  procedure sub_attachment(conn     in out nocopy utl_smtp.connection,
                           filename in varchar2,
                           dt_name  in varchar2) is
    l_filename varchar2(1000);
    l_amount   number;
  begin
    sub_drop_directory(dt_name);
    --创建directory
    sub_create_directory(dt_name, sub_get_file(filename, 1));
    --得到附件文件名称
    l_filename := sub_get_file(filename, 2);
    l_temp     := l_temp || l_crlf || '--' || l_boundary_mail || l_crlf;
    l_temp     := l_temp || 'Content-Type: ' || l_attachment_mime_type || ';
    name="' || l_filename || '"
Content-Transfer-Encoding: ' || l_attachment_encode || '
Content-Disposition: ' || l_attachment_disposition || ';
    filename="' || l_filename || '"' || l_crlf || l_crlf;
    utl_smtp.write_raw_data(l_connection,
                            utl_raw.cast_to_raw(convert(l_temp,
                                                        l_write_encode)));
    --begin
    --begin
    --把附件分成多份，这样可以发送超过32k的附件
    l_filepos  := 1; --重置offset，在发送多个附件时，必须重置
    l_fil      := bfilename(dt_name, l_filename);
    l_file_len := dbms_lob.getlength(l_fil);
    l_modulo   := mod(l_file_len, l_amt);
    l_pieces   := trunc(l_file_len / l_amt);
    if (l_modulo <> 0) then
      l_pieces := l_pieces + 1;
    end if;
    dbms_lob.fileopen(l_fil, dbms_lob.file_readonly);
    l_data   := null;
    l_amount := l_amt;
    for i in 1 .. l_pieces loop
      dbms_lob.read(l_fil, l_amount, l_filepos, l_buf);
      l_filepos  := i * l_amount + 1;
      l_file_len := l_file_len - l_amount;
      utl_smtp.write_raw_data(conn, utl_encode.base64_encode(l_buf));
      if i = l_pieces then
        l_amount := l_file_len;
      end if;
    end loop;
    dbms_lob.fileclose(l_fil);
    /*exception
      when others then
        dbms_lob.fileclose(l_fil);
        sub_end_boundary(conn);
        raise;
    end; --结束处理二进制附件*/
    sub_drop_directory(dt_name);
  end; --结束过程attachment
  procedure sub_send_mail is
    l_from varchar2(1000) := '<' || p_from || '>';
  begin
    l_connection := utl_smtp.open_connection(p_smtp_hostname,
                                             p_smtp_portnum);
    utl_smtp.helo(l_connection, p_smtp_hostname);
    /* smtp服务器登录校验 */
    if p_need_valid = 'Y' then
      utl_smtp.command(l_connection, 'AUTH LOGIN', '');
      utl_smtp.command(l_connection,
                       utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_user_name))));
      utl_smtp.command(l_connection,
                       utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_user_pwd))));
    end if;
    utl_smtp.mail(l_connection, l_from);
    sub_splite_str(p_to); --处理邮件地址
    for k in 1 .. my_address_list.count loop
      l_receivers := l_receivers || my_address_list(k) || ';';
      utl_smtp.rcpt(l_connection, my_address_list(k));
    end loop;
    l_temp := l_temp || 'From: ' || l_from || l_crlf;
    l_temp := l_temp || 'To: ' || l_receivers || l_crlf;
    --l_temp := l_temp || 'Cc: ' || l_receivers || l_crlf;--抄送
    --l_temp := l_temp || 'Bcc: ' || l_receivers || l_crlf;--密送
    l_temp := l_temp || 'Subject: ' || p_subject || l_crlf;
    --l_temp := l_temp || 'X-Mailer: Foxmail 7, 1, 3, 48[cn]' || l_crlf;--发送客户端
    l_temp := l_temp || 'Content-Type: multipart/mixed; boundary="' ||
              l_boundary_mail || '"' || l_crlf;
    l_temp := l_temp || 'MIME-Version: 1.0' || l_crlf || l_crlf;
    l_temp := l_temp || '--' || l_boundary_mail || l_crlf;
    if nvl(p_attachment_path, ' ') <> ' ' then
      l_temp := l_temp || 'content-type: multipart/alternative; boundary="' ||
                l_boundary_content || '"' || l_crlf || l_crlf || l_crlf;
      l_temp := l_temp || '--' || l_boundary_content || l_crlf;
    end if;
    --开始
    dbms_lob.createtemporary(l_body_html, false, 10);
    dbms_lob.write(l_body_html, length(l_temp), 1, l_temp);
    --文本内容
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    l_temp   := 'content-type: text/plain; charset="GB2312"; Content-Transfer-Encoding: base64' ||
                l_crlf || l_crlf;
    dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);
    if trim(p_text) is not null then
      dbms_lob.append(l_body_html, p_text);
    end if;
    --html内容
    if nvl(p_attachment_path, ' ') <> ' ' then
      l_temp := l_crlf || l_crlf || '--' || l_boundary_content ||'--' || l_crlf;
    else
      l_temp := l_crlf || l_crlf || '--' || l_boundary_mail ||'--' || l_crlf;
    end if;
    l_temp   := l_temp ||
                'content-type: text/html;charset="GB2312";Content-Transfer-Encoding: quoted-printable' ||
                l_crlf || l_crlf;
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);
    dbms_lob.append(l_body_html, nvl(p_html, ' '));
    --content结束
    if nvl(p_attachment_path, ' ') <> ' ' then
      l_temp := l_crlf || l_crlf || '--' || l_boundary_content || '--' ||
                l_crlf || l_crlf;
    else
      l_temp := l_crlf || '--' || l_boundary_mail || '--' || l_crlf ||
                l_crlf;
    end if;
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);
    --写入邮件
    l_offset  := 1;
    l_ammount := 1900;
    utl_smtp.open_data(l_connection);
    while l_offset < dbms_lob.getlength(l_body_html) loop
      utl_smtp.write_raw_data(l_connection,
                              utl_raw.cast_to_raw(convert(dbms_lob.substr(l_body_html,
                                                                          l_ammount,
                                                                          l_offset),
                                                          l_write_encode)));
      l_offset  := l_offset + l_ammount;
      l_ammount := least(1900, dbms_lob.getlength(l_body_html) - l_ammount);
    end loop;
    commit;
    ----------------------------------------------------
    l_temp := null;
    --附件
    --如果文件名称不为空，则发送附件
    if (p_attachment_path is not null) then
      --根据逗号或者分号拆分附件地址
      sub_splite_str(p_attachment_path, 2);
      --循环发送附件(在同一个邮件中)
      for k in 1 .. my_acct_list.count loop
        sub_attachment(conn     => l_connection,
                       filename => my_acct_list(k),
                       dt_name  => l_directory_base_name || to_char(k));
        l_temp := l_crlf;
      end loop;
    end if;
    l_temp := l_crlf || l_crlf || '--' || l_boundary_mail || '--' || l_crlf ||
              l_crlf;
    utl_smtp.write_raw_data(l_connection,
                            utl_raw.cast_to_raw(convert(l_temp,
                                                        l_write_encode)));
    commit;
    utl_smtp.close_data(l_connection);
    utl_smtp.quit(l_connection);
    --utl_smtp.close_connection(l_connection);
    dbms_lob.freetemporary(l_body_html);
  end;

begin
  sub_send_mail();
  /*exception
  when others then
  null;*/
end;