使用ebs进行邮件发送

邮件发送类型分为两种

1、发送文章内容较短，使用varchar类型即可

2、如果发送内容相对繁琐，那么就需要使用clob类型的数据

发送邮件的核心代码

注意：本次分享是按照clob类型进行的。如果想使用varchar或者long类型可将报文改成响应类型

PROCEDURE send_attach(p_recipient_address IN VARCHAR2,--收件人
                        p_body              IN OUT CLOB,--正文
                        p_count             IN NUMBER,--长度
                        p_user_name         IN VARCHAR2,--收件人
                        p_rtn_code          OUT VARCHAR2) IS
  
    l_sender    VARCHAR2(30) := '123@163.com';--发件人邮箱
    l_recipient VARCHAR2(300) := p_recipient_address; --收件人
    l_subject   VARCHAR2(2000) := '测试邮件发送'; --主题
    --l_body      LONG := ' ' || p_body || '<br><br><br>本邮件由系统自动发送，请不要回复本邮件。';
    l_mail_conn utl_smtp.connection;
    l_mail_host VARCHAR2(30) := 'mail.163.com'; --发送邮箱的host
    l_user_name VARCHAR2(156) := '123@163.com';--同发件人
    l_user_pwd  VARCHAR2(156) := '1233344'; --发件人密码
  
    l_boundary       CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';
    l_first_boundary CONSTANT VARCHAR2(256) := '--' || l_boundary ||
                                               utl_tcp.crlf;
  
    --发送带有附件邮件，MIME必须设为multipart/mixed
    l_multipart_mime_type CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="' ||
                                                    l_boundary || '"';
  
    l_ehlo_result     utl_smtp.replies;
    l_starttls_result utl_smtp.reply;
  
    l_rtn_code VARCHAR2(10);
    --拆分clob
    offset    NUMBER := 1;
    lc_buffer VARCHAR2(32767);
    l_count   NUMBER := 20000;
  BEGIN
    
    --登陆认证语句 
    dbms_lob.append(p_body,
                    '<br><br><br>本邮件由系统自动发送，请不要回复本邮件。');
    p_rtn_code := 'S';
  
    l_mail_conn := utl_smtp.open_connection(l_mail_host, 25);
    utl_smtp.helo(l_mail_conn, l_mail_host);
    utl_smtp.helo(l_mail_conn, l_mail_host);
  
    utl_smtp.auth(c        => l_mail_conn,
                  username => l_user_name,
                  password => l_user_pwd,
                  schemes  => 'LOGIN');
    --指定发件人
    utl_smtp.mail(l_mail_conn, l_sender); --发件人
    utl_smtp.rcpt(l_mail_conn, l_recipient); --收件人
    utl_smtp.open_data(l_mail_conn);
    utl_smtp.write_data(l_mail_conn, 'From:' || l_sender || utl_tcp.crlf); --显示
    utl_smtp.write_data(l_mail_conn, 'To:' || l_recipient || utl_tcp.crlf); --显示
    --中文编码转换
    utl_smtp.write_raw_data(l_mail_conn,
                            utl_raw.cast_to_raw(convert('Subject:' ||
                                                        l_subject ||
                                                        utl_tcp.crlf,
                                                        'ZHS16GBK')));
    utl_smtp.write_raw_data(l_mail_conn,
                            utl_raw.cast_to_raw(convert('Content-Type:' ||
                                                        l_multipart_mime_type ||
                                                        utl_tcp.crlf,
                                                        'ZHS16GBK')));
    --utl_tcp.CRLF 数据流行尾符
    utl_smtp.write_data(l_mail_conn, utl_tcp.crlf);
    --邮件正文
    utl_smtp.write_data(l_mail_conn, l_first_boundary);
  
    utl_smtp.write_raw_data(l_mail_conn,
                            utl_raw.cast_to_raw(convert('Content-Type:text/html;charset=GB2312' ||
                                                        utl_tcp.crlf,
                                                        'ZHS16GBK')));
    utl_smtp.write_data(l_mail_conn, utl_tcp.crlf);
    -- 长文件拆分
    WHILE offset <= dbms_lob.getlength(p_body) LOOP
      dbms_lob.read(p_body, l_count, offset, lc_buffer);
      offset := offset + l_count;
      utl_smtp.write_raw_data(l_mail_conn,
                              utl_raw.cast_to_raw(convert(lc_buffer,
                                                          'ZHS16GBK')));
    END LOOP;
    --
    /*utl_smtp.write_raw_data(l_mail_conn,
    utl_raw.cast_to_raw(convert(l_body, 'ZHS16GBK')));*/
    utl_smtp.write_data(l_mail_conn, utl_tcp.crlf);
    --关闭连接
    utl_smtp.close_data(l_mail_conn);
    utl_smtp.quit(l_mail_conn);
  EXCEPTION
    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
      fnd_file.put_line(fnd_file.log,
                        '2 p_recipient_address=' || p_recipient_address);
    
      dbms_output.put_line(SQLERRM);
      fnd_file.put_line(fnd_file.log, '2 ' || SQLERRM);
    
      utl_smtp.quit(l_mail_conn);
      p_rtn_code := 'E';
    WHEN OTHERS THEN
      fnd_fi