PL/SQL通过UTL_SMTP实现带可选附件邮件发送及报错排查

现有代码报错核心原因 #

当前调用直接抛错、功能无法正常运行的根因有以下几点：

• 存储过程声明段缺失l_step变量定义，循环分块读取BLOB时直接引用未声明变量，导致存储过程编译失败，调用时直接抛出异常
• MIME根类型配置错误：带附件的邮件不能使用multipart/alternative类型（该类型仅用于纯文本/HTML双版本正文场景），必须使用multipart/mixed，否则邮件客户端无法正确识别附件
• 附件分块大小未定义：UTL_SMTP单次写入数据有长度限制，未合理设置分块大小会导致写入失败或附件截断
• 正文字符集写死为iso-8859-1，发送中文内容会出现乱码
• 附件MIME类型存储错误：txt、pdf、doc三类文件的标准MIME类型不是表中存的application/open....，错误的MIME会导致客户端无法正常打开附件
• 调用逻辑冗余：同一条附件数据分三次查询，存在读一致性风险且性能浪费

修复后可直接使用的存储过程 #

该版本兼容可选附件逻辑：传附件参数就发带附件的邮件，不传就只发HTML正文，支持pdf/doc/txt三类格式：

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_subject   IN VARCHAR2,
                                       p_html_msg  IN VARCHAR2 DEFAULT NULL,
                                       p_attach_name IN VARCHAR2 DEFAULT NULL,
                                       p_attach_mime IN VARCHAR2 DEFAULT NULL,
                                       p_attach_blob IN BLOB DEFAULT NULL,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
  l_step        INTEGER := 1200; -- 分块大小，适配base64编码后的长度限制
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);
  
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  -- 修正MIME类型为mixed，兼容正文+附件场景
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
    
  -- 写入HTML正文
  IF p_html_msg IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="utf-8"' || UTL_TCP.crlf || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, p_html_msg);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;

  -- 写入附件（仅当传入附件参数时执行）
  IF p_attach_name IS NOT NULL AND p_attach_blob IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

    -- 循环分块写入BLOB，避免单包过长报错
    FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) LOOP
      UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))) || UTL_TCP.crlf);
    END LOOP;

    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf);
  END IF;

  -- 写入MIME结束标记
  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;
/

修复后的调用示例 #

带附件调用（支持txt/pdf/doc） #

注意三类附件的标准MIME类型不要写错，建议先修正attachment_details表中错误的mimetype值：

• txt对应：text/plain
• pdf对应：application/pdf
• doc对应：application/msword

DECLARE
  l_name attachment_details.filename%TYPE;
  l_blob attachment_details.fblob%TYPE;
  l_mime attachment_details.mimetype%TYPE;
BEGIN
  -- 一次查询取出所有附件字段，避免多次查询的一致性问题
  SELECT filename, fblob, mimetype
  INTO   l_name, l_blob, l_mime
  FROM   attachment_details
  WHERE  filename = 'ABC.txt';

  send_mail(p_to          => 'me@mycompany.com',
            p_from        => 'admin@mycompany.com',
            p_subject     => 'Test Message',
            p_html_msg    => '<html> <body> <h1> Hi friends !!! </h1> </body> </html>',
            p_attach_name => l_name ,
            p_attach_mime => l_mime ,
            p_attach_blob => l_blob,
            p_smtp_host   => 'smtp.mycompany.com');
END;
/

无附件调用（仅发HTML正文） #

不传三个附件参数即可，存储过程会自动跳过附件分支：

BEGIN
  send_mail(p_to          => 'me@mycompany.com',
            p_from        => 'admin@mycompany.com',
            p_subject     => 'Test Message No Attach',
            p_html_msg    => '<html> <body> <h1> This mail has no attachment </h1> </body> </html>',
            p_smtp_host   => 'smtp.mycompany.com');
END;
/

注意事项 #

• 单封邮件附件总大小不要超过SMTP服务器配置的上限，一般建议控制在10MB以内，过大的附件会导致发送超时或被SMTP服务器拒收
• 确保Oracle数据库服务器能正常连通SMTP服务器的25端口，网络不通会直接报连接错误
• 如果发送中文文件名、中文正文出现乱码，需要额外对主题、文件名做mime头编码处理

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