Oracle UTL_SMTP 發送郵件實戰：從基礎文本到 HTML 郵件、附件擴展架構

前言

Oracle 10g+ 提供 UTL_MAIL 簡化郵件發送，但 UTL_SMTP 兼容性更廣 (8i+)、可控性更強，且支援自定義 MIME 結構 (HTML、附件、內嵌圖片)。本文提供生產級 SENDMAIL 套件完整碼，涵蓋文本/HTML 郵件、錯誤處理、擴展藍圖。

一、前置條件

1. 確認 UTL_SMTP、UTL_TCP 已安裝 (SYS schema 下)：

   SELECT object_name, status FROM dba_objects
   WHERE object_name IN ('UTL_SMTP','UTL_TCP') AND owner = 'SYS';
   

   缺失時執行 $ORACLE_HOME/rdbms/admin/utlsmtp.sql、utltcp.sql
2. 取得企業 SMTP 伺服器位址 (非加密連接埠 25/587)、**注意：Gmail/Outlook 等需 OAuth/TLS 的郵箱不支援**
3. 網路通達性：DB Server 能 Telnet SMTP 埠

二、核心套件規格

CREATE OR REPLACE PACKAGE sendmail IS
  PROCEDURE send (
    p_sender    VARCHAR2,
    p_recipient VARCHAR2,
    p_subject   VARCHAR2,
    p_body      VARCHAR2 DEFAULT NULL
  );
END sendmail;
/

三、套件主體：模組化設計 (Common + Send)

CREATE OR REPLACE PACKAGE BODY sendmail IS

  -- 私有核心流程：建立連線、SMTP 握手、寄件者/收件者、Data 起始
  PROCEDURE common (
    p_sender    VARCHAR2,
    p_recipient VARCHAR2,
    p_subject   VARCHAR2,
    c           OUT UTL_SMTP.CONNECTION
  ) IS
    v_recipient VARCHAR2(1000) := p_recipient;
  BEGIN
    -- 1. 連線 SMTP (請替換為實際主機)
    c := UTL_SMTP.OPEN_CONNECTION('smtp.yourcompany.com', 25);

    -- 2. HELO/EHLO
    UTL_SMTP.HELO(c, 'yourcompany.com');

    -- 3. MAIL FROM
    UTL_SMTP.MAIL(c, p_sender);

    -- 4. RCPT TO (支援多收件人，以逗號分隔)
    FOR r IN (SELECT TRIM(column_value) addr
              FROM TABLE(CAST(STR2TBL(v_recipient) AS TBL_VARCHAR2)))
    LOOP
      UTL_SMTP.RCPT(c, r.addr);
    END LOOP;

    -- 5. 開始 DATA 區塊
    UTL_SMTP.OPEN_DATA(c);

    -- 6. 標準 Header
    UTL_SMTP.WRITE_DATA(c, 'From: ' || p_sender || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(c, 'To: ' || p_recipient || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(c, 'Subject: ' || p_subject || UTL_TCP.CRLF);

  EXCEPTION
    WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
      UTL_SMTP.QUIT(c);
      RAISE;
    WHEN OTHERS THEN
      RAISE;
  END common;

  -- 公開介面：發送 HTML 郵件
  PROCEDURE send (
    p_sender    VARCHAR2,
    p_recipient VARCHAR2,
    p_subject   VARCHAR2,
    p_body      VARCHAR2 DEFAULT NULL
  ) IS
    c UTL_SMTP.CONNECTION;
  BEGIN
    common(p_sender, p_recipient, p_subject, c);

    -- Content-Type: text/html 允許 HTML 標籤
    UTL_SMTP.WRITE_DATA(c, 'Content-Type: text/html; charset=utf-8' || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || p_body);

    UTL_SMTP.CLOSE_DATA(c);
    UTL_SMTP.QUIT(c);

  EXCEPTION
    WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
      UTL_SMTP.QUIT(c);
      RAISE;
    WHEN OTHERS THEN
      RAISE;
  END send;

END sendmail;
/

四、輔助型別：字串分割支援多收件人

CREATE OR REPLACE TYPE tbl_varchar2 AS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION str2tbl(p_str VARCHAR2, p_delim VARCHAR2 DEFAULT ',')
RETURN tbl_varchar2 PIPELINED IS
  v_idx  PLS_INTEGER := 1;
  v_len  PLS_INTEGER;
BEGIN
  LOOP
    v_len := INSTR(p_str, p_delim, v_idx);
    IF v_len = 0 THEN
      PIPE ROW(TRIM(SUBSTR(p_str, v_idx)));
      EXIT;
    ELSE
      PIPE ROW(TRIM(SUBSTR(p_str, v_idx, v_len - v_idx)));
      v_idx := v_len + LENGTH(p_delim);
    END IF;
  END LOOP;
  RETURN;
END;
/

五、調用範例

BEGIN
  sendmail.send(
    p_sender    => 'alert@yourcompany.com',
    p_recipient => 'dba1@yourcompany.com,dba2@yourcompany.com',
    p_subject   => '【告警】生產庫表空間剩餘 < 10%',
    p_body      => '
表空間告警
' ||
                   '
表空間 USERS 剩餘空間僅 8.2%，請立即擴容。
' ||
                   '
查看詳情
'
  );
END;
/

六、擴展藍圖：發送 BLOB 附件 (多部分 MIME)

在 common 後續寫入 MIME-Version: 1.0、Content-Type: multipart/mixed，再分 Boundary 寫入 HTML 部分與 Base64 編碼的附件部分。核心邏輯：

UTL_SMTP.WRITE_DATA(c, 'Content-Type: multipart/mixed; boundary="====BOUNDARY===="' || UTL_TCP.CRLF);
-- HTML 部分
UTL_SMTP.WRITE_DATA(c, '--====BOUNDARY====' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c, 'Content-Type: text/html; charset=utf-8' || UTL_TCP.CRLF || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c, p_html_body || UTL_TCP.CRLF);
-- 附件部分
UTL_SMTP.WRITE_DATA(c, '--====BOUNDARY====' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c, 'Content-Type: application/pdf; name="report.pdf"' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c, 'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c, 'Content-Disposition: attachment; filename="report.pdf"' || UTL_TCP.CRLF || UTL_TCP.CRLF);
UTL_SMTP.WRITE_RAW_DATA(c, UTL_ENCODE.BASE64_ENCODE(p_blob)); -- 需分塊寫入
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || '--====BOUNDARY====--' || UTL_TCP.CRLF);

七、常見問題與對策

八、總結

UTL_SMTP 雖屬低階 API，但透過模組化封裝 (Common + Send) 可構建穩健、可擴展的郵件發送服務。**生產環境建議**：

1. 統一由「郵件網關/中介服務」發送，DB 僅寫入佇列表，避免 DB 直接對外連網
2. 關鍵告警採用「DB 寫表 + 獨立發送器輪詢」架構，解耦且可重試
3. 定期巡檢發送日誌表，監控發送成功率