PL/SQL使用UTL_SMTP发送多个超32K附件仅第一个生效求助

问题原因 #

核心错误是你提前写入了MIME多部分内容的结束标记：

• 格式为--[边界名]--（末尾带两个短横线）的标记是MIME协议规定的整个多部分内容的结束标识，你在第一个附件写完后就写入了这个标记，邮件客户端解析时会认为整个邮件内容到这里就结束了，直接忽略后面所有内容，自然不会显示第二个附件。
• 次要问题是附件缺少Content-Type和传输编码声明，部分客户端可能会出现附件内容乱码或者识别异常。

修正方案 #

1. 删除第一个附件结尾处的结束边界写入语句，仅在所有附件（以及邮件正文）都写完之后，写一次结束边界即可。
2. 补充附件的必要头信息，避免识别异常。

修正后的核心代码段如下：

l_clob2 clob; // contains data for attachment 1
l_clob  clob; // contains data  for attachment 2
c_mime_boundary clob := '-----AABCDEFBBCCC0123456789DE';
v_index integer;
v_len integer;
v_index1 integer;
v_len1 integer;

v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.OPEN_DATA(v_Mail_Conn);

UTL_SMTP.write_data(v_Mail_Conn, 'From: ' || v_From || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn, 'To: ' || v_Recipient || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn, 'Subject: ' || REPLACE(v_Subject, '[DATE]',TO_CHAR(sysdate,'DD.MM.YYYY')) || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn,  'Content-Type: multipart/mixed; boundary="' || c_mime_boundary || '"' || UTL_TCP.crlf);
  
-- 邮件正文
UTL_SMTP.write_data(v_Mail_Conn, '--' || c_mime_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn, V_mail_message );
UTL_SMTP.write_data(v_Mail_Conn, UTL_TCP.crlf || UTL_TCP.crlf);
  
-- 第一个附件头
UTL_SMTP.write_data(v_Mail_Conn, '--' || c_mime_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn, 'Content-Type: text/csv; charset="utf-8"' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn, 'Content-Disposition: attachment; filename="' || 'file1.csv' || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn, 'Content-Transfer-Encoding: 8bit' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn, UTL_TCP.crlf);

-- 写入第一个附件内容
v_len := DBMS_LOB.getlength(l_clob2);
v_index := 1;
WHILE v_index <= v_len
LOOP
  UTL_SMTP.write_data(v_Mail_Conn, DBMS_LOB.SUBSTR(l_clob2, 32000, v_index));
  v_index := v_index + 32000;
END LOOP;
UTL_SMTP.write_data(v_Mail_Conn, UTL_TCP.crlf);
  
-- 第二个附件头
UTL_SMTP.write_data(v_Mail_Conn, '--' || c_mime_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn, 'Content-Type: text/csv; charset="utf-8"' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn, 'Content-Disposition: attachment; filename="' || 'file2.csv' || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn, 'Content-Transfer-Encoding: 8bit' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_Mail_Conn, UTL_TCP.crlf);
  
-- 写入第二个附件内容
v_len1 := DBMS_LOB.getlength(l_clob);
v_index1 := 1;
WHILE v_index1 <= v_len1
LOOP
  UTL_SMTP.write_data(v_Mail_Conn, DBMS_LOB.SUBSTR(l_clob, 32000, v_index1));
  v_index1 := v_index1 + 32000;
END LOOP;
UTL_SMTP.write_data(v_Mail_Conn, UTL_TCP.crlf);

-- 所有内容写完后，仅在这里写一次结束边界
UTL_SMTP.write_data(v_Mail_Conn, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);
  
utl_smtp.CLOSE_DATA(v_mail_conn);
utl_smtp.Quit(v_mail_conn);

内容的提问来源于stack exchange，提问作者Thejus32