如何在Oracle中通过UTL_SMTP包发送带Excel附件的邮件并求代码

要通过UTL_SMTP发送带Excel附件的邮件，核心是构建MIME多部分邮件，将Excel文件的二进制内容编码为Base64格式（邮件传输仅支持文本内容），并通过边界标识区分正文与附件，正确设置邮件头参数。

关键前置准备 #

• 确保当前用户拥有UTL_SMTP、UTL_ENCODE、UTL_RAW、UTL_FILE等包的执行权限，若没有需联系DBA授权
• 若读取服务器本地Excel文件，需提前创建Oracle目录对象并授予读写权限

完整代码示例 #

DECLARE
  l_smtp_host     VARCHAR2(100) := 'smtp.yourdomain.com'; -- 替换为你的SMTP服务器地址
  l_smtp_port     PLS_INTEGER := 25; -- SMTP端口，常用25/587
  l_from          VARCHAR2(100) := 'sender@yourdomain.com'; -- 发件人邮箱
  l_to            VARCHAR2(100) := 'recipient@yourdomain.com'; -- 收件人邮箱
  l_subject       VARCHAR2(200) := '测试邮件：带Excel附件';
  l_boundary      VARCHAR2(50) := '----=_NextPart_' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF');
  l_mail_conn     UTL_SMTP.CONNECTION;
  l_excel_blob    BLOB;
  l_excel_clob    CLOB;
  l_offset        PLS_INTEGER := 1;
  l_chunk_size    PLS_INTEGER := 76; -- Base64编码每行最大字符数
  l_temp_varchar  VARCHAR2(32767);
BEGIN
  -- 1. 读取服务器上的Excel文件到BLOB
  -- 需提前创建目录对象：CREATE DIRECTORY EXCEL_DIR AS '/your/excel/file/path/';
  DECLARE
    l_file_handle UTL_FILE.FILE_TYPE;
    l_buffer      RAW(32767);
    l_amount      PLS_INTEGER := 32767;
  BEGIN
    l_file_handle := UTL_FILE.FOPEN('EXCEL_DIR', 'test.xlsx', 'rb', 32767);
    DBMS_LOB.CREATETEMPORARY(l_excel_blob, TRUE);
    LOOP
      BEGIN
        UTL_FILE.GET_RAW(l_file_handle, l_buffer, l_amount);
        DBMS_LOB.WRITEAPPEND(l_excel_blob, UTL_RAW.LENGTH(l_buffer), l_buffer);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
      END;
    END LOOP;
    UTL_FILE.FCLOSE(l_file_handle);
  END;

  -- 2. 将BLOB转换为Base64编码的CLOB
  DBMS_LOB.CREATETEMPORARY(l_excel_clob, TRUE);
  WHILE l_offset <= DBMS_LOB.GETLENGTH(l_excel_blob) LOOP
    l_temp_varchar := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(l_excel_blob, l_chunk_size*3, l_offset)));
    DBMS_LOB.WRITEAPPEND(l_excel_clob, LENGTH(l_temp_varchar), l_temp_varchar || CHR(13) || CHR(10));
    l_offset := l_offset + l_chunk_size*3;
  END WHILE;

  -- 3. 连接SMTP服务器并初始化邮件
  l_mail_conn := UTL_SMTP.OPEN_CONNECTION(l_smtp_host, l_smtp_port);
  UTL_SMTP.HELO(l_mail_conn, l_smtp_host);
  UTL_SMTP.MAIL(l_mail_conn, l_from);
  UTL_SMTP.RCPT(l_mail_conn, l_to);
  UTL_SMTP.DATA(l_mail_conn);

  -- 4. 发送邮件头
  UTL_SMTP.WRITE_DATA(l_mail_conn, 'From: ' || l_from || CHR(13) || CHR(10));
  UTL_SMTP.WRITE_DATA(l_mail_conn, 'To: ' || l_to || CHR(13) || CHR(10));
  UTL_SMTP.WRITE_DATA(l_mail_conn, 'Subject: ' || l_subject || CHR(13) || CHR(10));
  UTL_SMTP.WRITE_DATA(l_mail_conn, 'MIME-Version: 1.0' || CHR(13) || CHR(10));
  UTL_SMTP.WRITE_DATA(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || CHR(13) || CHR(10));
  UTL_SMTP.WRITE_DATA(l_mail_conn, CHR(13) || CHR(10));

  -- 5. 发送邮件正文
  UTL_SMTP.WRITE_DATA(l_mail_conn, '--' || l_boundary || CHR(13) || CHR(10));
  UTL_SMTP.WRITE_DATA(l_mail_conn, 'Content-Type: text/plain; charset=UTF-8' || CHR(13) || CHR(10));
  UTL_SMTP.WRITE_DATA(l_mail_conn, CHR(13) || CHR(10));
  UTL_SMTP.WRITE_DATA(l_mail_conn, '这是邮件正文，附件为Excel测试文件。' || CHR(13) || CHR(10));
  UTL_SMTP.WRITE_DATA(l_mail_conn, CHR(13) || CHR(10));

  -- 6. 发送Excel附件
  UTL_SMTP.WRITE_DATA(l_mail_conn, '--' || l_boundary || CHR(13) || CHR(10));
  -- 若为.xlsx格式，Content-Type改为application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
  UTL_SMTP.WRITE_DATA(l_mail_conn, 'Content-Type: application/vnd.ms-excel; name="test.xlsx"' || CHR(13) || CHR(10));
  UTL_SMTP.WRITE_DATA(l_mail_conn, 'Content-Disposition: attachment; filename="test.xlsx"' || CHR(13) || CHR(10));
  UTL_SMTP.WRITE_DATA(l_mail_conn, 'Content-Transfer-Encoding: base64' || CHR(13) || CHR(10));
  UTL_SMTP.WRITE_DATA(l_mail_conn, CHR(13) || CHR(10));

  -- 分块发送Base64编码内容
  l_offset := 1;
  WHILE l_offset <= DBMS_LOB.GETLENGTH(l_excel_clob) LOOP
    l_temp_varchar := DBMS_LOB.SUBSTR(l_excel_clob, 32767, l_offset);
    UTL_SMTP.WRITE_DATA(l_mail_conn, l_temp_varchar);
    l_offset := l_offset + 32767;
  END WHILE;
  UTL_SMTP.WRITE_DATA(l_mail_conn, CHR(13) || CHR(10));

  -- 7. 结束邮件内容
  UTL_SMTP.WRITE_DATA(l_mail_conn, CHR(13) || CHR(10) || '--' || l_boundary || '--' || CHR(13) || CHR(10));
  UTL_SMTP.DATA_END(l_mail_conn);
  UTL_SMTP.QUIT(l_mail_conn);

  -- 清理临时对象
  DBMS_LOB.FREETEMPORARY(l_excel_blob);
  DBMS_LOB.FREETEMPORARY(l_excel_clob);

EXCEPTION
  WHEN OTHERS THEN
    -- 异常回滚与清理
    IF UTL_SMTP.IS_OPEN(l_mail_conn) THEN
      UTL_SMTP.QUIT(l_mail_conn);
    END IF;
    DBMS_LOB.FREETEMPORARY(l_excel_blob);
    DBMS_LOB.FREETEMPORARY(l_excel_clob);
    RAISE;
END;
/

额外说明 #

• 若SMTP服务器需要身份认证，需在HELO后添加UTL_SMTP.AUTH(l_mail_conn, 'smtp_username', 'smtp_password');
• 若Excel数据来自数据库查询，可通过DBMS_SQL或UTL_FILE生成CSV后转换为Excel，或直接生成Excel格式的BLOB
• 边界字符串需确保不会与邮件正文/附件内容重复，示例中用时间戳保证唯一性

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