您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

使用 UTL_SMTP 發送含 BLOB 附件的電子郵件實作

編輯：Oracle 資料庫基礎

需求背景

企業常需將資料庫中儲存的 PDF、Excel 等二進位大物件（BLOB）以郵件附件形式寄送給客戶或內部人員。本文示範如何擴充自訂 PL/SQL 套件，新增 SEND_BLOB 程序，實現帶附件的郵件發送。

套件規格（Package Specification）

CREATE OR REPLACE PACKAGE mail_pkg AS
  PROCEDURE send_blob (
    p_sender     IN VARCHAR2,  -- 寄件者
    p_recipient  IN VARCHAR2,  -- 收件者
    p_subject    IN VARCHAR2,  -- 主旨
    p_filename   IN VARCHAR2,  -- 附件檔名
    p_blob       IN BLOB       -- 附件二進位內容
  );
END mail_pkg;

套件主體（Package Body）核心邏輯

CREATE OR REPLACE PACKAGE BODY mail_pkg AS

  PROCEDURE send_blob (
    p_sender     IN VARCHAR2,
    p_recipient  IN VARCHAR2,
    p_subject    IN VARCHAR2,
    p_filename   IN VARCHAR2,
    p_blob       IN BLOB
  ) IS
    l_conn           UTL_SMTP.CONNECTION;
    l_smtp_host      VARCHAR2(100) := 'smtp.example.com';
    l_smtp_port      NUMBER := 25;
    l_boundary       VARCHAR2(50) := '----ORACLE_BLOB_BOUNDARY_' || DBMS_RANDOM.STRING('X', 10);
    l_buffer         RAW(32767);
    l_amount         BINARY_INTEGER := 32767;
    l_pos            INTEGER := 1;
    l_blob_len       INTEGER;
    l_encoded        VARCHAR2(32767);
  BEGIN
    l_blob_len := DBMS_LOB.GETLENGTH(p_blob);

    -- 1. 建立 SMTP 連線
    l_conn := UTL_SMTP.OPEN_CONNECTION(l_smtp_host, l_smtp_port);
    UTL_SMTP.HELO(l_conn, l_smtp_host);
    UTL_SMTP.MAIL(l_conn, p_sender);
    UTL_SMTP.RCPT(l_conn, p_recipient);
    UTL_SMTP.OPEN_DATA(l_conn);

    -- 2. 寫入郵件標頭
    UTL_SMTP.WRITE_DATA(l_conn, 'Subject: ' || p_subject || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, 'From: ' || p_sender || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, 'To: ' || p_recipient || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, 'MIME-Version: 1.0' || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.CRLF || UTL_TCP.CRLF);

    -- 3. 郵件本文部分
    UTL_SMTP.WRITE_DATA(l_conn, '--' || l_boundary || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, 'Content-Type: text/plain; charset=UTF-8' || UTL_TCP.CRLF || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, '您好，附件為系統自動發送的報表檔案。' || UTL_TCP.CRLF || UTL_TCP.CRLF);

    -- 4. 附件部分
    UTL_SMTP.WRITE_DATA(l_conn, '--' || l_boundary || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, 'Content-Type: application/octet-stream; name="' || p_filename || '"' || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, 'Content-Disposition: attachment; filename="' || p_filename || '"' || UTL_TCP.CRLF || UTL_TCP.CRLF);

    -- 5. 分塊讀取 BLOB 並 Base64 編碼寫入
    WHILE l_pos <= l_blob_len LOOP
      DBMS_LOB.READ(p_blob, l_amount, l_pos, l_buffer);
      l_encoded := UTL_ENCODE.BASE64_ENCODE(l_buffer);
      UTL_SMTP.WRITE_RAW_DATA(l_conn, UTL_RAW.CAST_TO_RAW(l_encoded || UTL_TCP.CRLF));
      l_pos := l_pos + l_amount;
    END LOOP;

    -- 6. 結束邊界
    UTL_SMTP.WRITE_DATA(l_conn, UTL_TCP.CRLF || '--' || l_boundary || '--' || UTL_TCP.CRLF);
    UTL_SMTP.CLOSE_DATA(l_conn);
    UTL_SMTP.QUIT(l_conn);

  EXCEPTION
    WHEN OTHERS THEN
      IF UTL_SMTP.IS_OPEN(l_conn) THEN
        UTL_SMTP.QUIT(l_conn);
      END IF;
      RAISE;
  END send_blob;

END mail_pkg;

呼叫範例

DECLARE
  l_blob BLOB;
BEGIN
  -- 假設從表中讀取 PDF 檔案
  SELECT file_content INTO l_blob FROM report_files WHERE report_id = 1001;
  mail_pkg.send_blob(
    p_sender    => 'system@example.com',
    p_recipient => 'user@example.com',
    p_subject   => '月度報表 - 2024-01',
    p_filename  => 'monthly_report_202401.pdf',
    p_blob      => l_blob
  );
END;