Oracle PL/SQL发送邮件时页脚图片偶尔模糊问题求助

使用Oracle PL/SQL编写邮件发送存储过程，将PNG格式公司logo作为内嵌资源放置在邮件页脚，多数情况下显示正常，但偶尔会出现图片模糊、细节失真的情况。

原代码片段 #

UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection,
                    'Content-Type: multipart/mixed; boundary="' ||
                    c_mime_boundary || '"' || UTL_TCP.crlf);

UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection,
                    'This is a multi-part message in MIME format.' ||
                    UTL_TCP.crlf);

UTL_SMTP.write_data(v_connection,
                    '--' || c_mime_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection,
                    'Content-Type: text/html; charset="UTF-8"' ||
                    UTL_TCP.crlf);

UTL_SMTP.write_data(v_connection, UTL_TCP.CRLF);

-----------------------------------   

ClobLen := DBMS_LOB.GETLENGTH(p_message);

LOOP
  EXIT WHEN offset > ClobLen;
  DBMS_LOB.READ(p_message || '<br><br>' || p_footer
  , amount, offset, BUFFER);
  UTL_SMTP.write_raw_data(v_connection, UTL_RAW.cast_to_raw(BUFFER));
  offset := offset + amount;
END LOOP;

UTL_SMTP.write_data(v_connection, UTL_TCP.CRLF);

IF p_attach_name IS NOT NULL THEN
  UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || UTL_TCP.crlf);
  UTL_SMTP.write_data(v_connection, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
  UTL_SMTP.write_data(v_connection, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_connection, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
  
  FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/offset) LOOP
    UTL_SMTP.write_data(v_connection, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, offset, i * offset + 1))) || UTL_TCP.crlf);
  END LOOP;

  UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
END IF;
-----------------------------------

UTL_SMTP.close_data(v_connection);
UTL_SMTP.quit(v_connection);

解决步骤 #

• 修正MIME类型为multipart/related：原代码使用multipart/mixed（用于分离的附件和正文），内嵌资源应使用multipart/related，确保邮件客户端能正确关联正文和内嵌图片。
  代码修改：

  UTL_SMTP.write_data(v_connection,
                      'Content-Type: multipart/related; boundary="' ||
                      c_mime_boundary || '"' || UTL_TCP.crlf);
  

• 为内嵌图片添加Content-ID并设置为inline：原代码将图片作为附件发送，应改为内嵌类型并指定唯一Content-ID，正文页脚通过cid:xxx引用该图片，避免客户端误压缩或渲染错误。
  代码修改示例：

  -- 替换原附件部分的Content-Disposition
  UTL_SMTP.write_data(v_connection, 'Content-Disposition: inline; filename="' || p_attach_name || '"' || UTL_TCP.crlf);
  -- 添加唯一Content-ID
  UTL_SMTP.write_data(v_connection, 'Content-ID: <logo@yourdomain.com>' || UTL_TCP.crlf);
  -- 页脚HTML中引用图片：<img src="cid:logo@yourdomain.com" alt="公司logo">
  

• 修复Base64编码分块长度：Base64编码每行标准长度为76字符，原代码未明确设置分块长度，可能导致编码块异常引发解码失真。
  代码修改：

  -- 定义标准Base64分块长度
  l_base64_chunk_size CONSTANT PLS_INTEGER := 76;
  -- 循环中使用标准分块长度
  FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_base64_chunk_size) LOOP
    UTL_SMTP.write_data(v_connection, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_base64_chunk_size, i * l_base64_chunk_size + 1))) || UTL_TCP.crlf);
  END LOOP;
  

• 修正CLOB读取逻辑：原代码在循环读取时动态拼接内容，易引发字符截断或编码错误。应先拼接为完整CLOB再读取：
  代码修改：

  -- 先创建完整的邮件内容CLOB
  l_full_html CLOB;
  DBMS_LOB.CREATETEMPORARY(l_full_html, TRUE);
  DBMS_LOB.WRITEAPPEND(l_full_html, LENGTH(p_message), p_message);
  DBMS_LOB.WRITEAPPEND(l_full_html, 8, '<br><br>');
  DBMS_LOB.WRITEAPPEND(l_full_html, DBMS_LOB.GETLENGTH(p_footer), p_footer);
  
  ClobLen := DBMS_LOB.GETLENGTH(l_full_html);
  offset := 1; -- LOB索引从1开始
  amount := 32767; -- 每次读取的最大字符数
  LOOP
    EXIT WHEN offset > ClobLen;
    DBMS_LOB.READ(l_full_html, amount, offset, BUFFER);
    UTL_SMTP.write_raw_data(v_connection, UTL_RAW.cast_to_raw(BUFFER));
    offset := offset + amount;
  END LOOP;
  DBMS_LOB.FREETEMPORARY(l_full_html);
  

• 检查图片源文件：确保嵌入的PNG为高清无压缩版本，避免使用本身分辨率不足或已压缩的图片。

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