Oracle 資料庫發送郵件的完整實作指南與程式碼範例

Oracle UTL_SMTP 發送郵件完整教學

在企業級應用中，資料庫主動發送通知郵件是常見需求。Oracle 透過 UTL_SMTP 套件提供原生支援，本文將以實戰範例詳細說明從環境準備、程序建立到實際發送的完整流程，並針對中文編碼、認證機制等常見坑點提供解決方案。

測試環境與前置條件

• 資料庫版本：Oracle 9i (9.2.0.4) / 10g (10.2.0.1)
• 作業系統：Linux AS3
• 郵件伺服器：Windows 2003 + WinMail
• 網路需求：Oracle 主機需能連通郵件伺服器 25 埠

核心發送程序：SEND_MAIL

以下 PL/SQL 程序支援 SMTP 認證（AUTH LOGIN）、中文主旨、附件編碼等進階功能：

CREATE OR REPLACE PROCEDURE SEND_MAIL (
    as_sender   IN VARCHAR2,  -- 寄件者
    as_recp     IN VARCHAR2,  -- 收件者
    as_subject  IN VARCHAR2,  -- 郵件主旨
    as_msg_body IN VARCHAR2   -- 郵件內文
) IS
    ls_mailhost   VARCHAR2(30) := 'mail.server.ip';  -- 郵件伺服器 IP 或域名
    lc_mail_conn  UTL_SMTP.CONNECTION;
    ls_subject    VARCHAR2(100);
    ls_msg_body   VARCHAR2(20000);
    ls_username   VARCHAR2(256) := 'smtp_user';      -- SMTP 帳號
    ls_passWord   VARCHAR2(256) := 'smtp_password';  -- SMTP 密碼
BEGIN
    -- 建立連線
    lc_mail_conn := UTL_SMTP.OPEN_CONNECTION(ls_mailhost, 25);
    UTL_SMTP.HELO(lc_mail_conn, ls_mailhost);
    
    -- SMTP 認證 (AUTH LOGIN 需 Base64 編碼)
    UTL_SMTP.COMMAND(lc_mail_conn, 'AUTH LOGIN');
    UTL_SMTP.COMMAND(lc_mail_conn, DEMO_BASE64.ENCODE(UTL_RAW.CAST_TO_RAW(ls_username)));
    UTL_SMTP.COMMAND(lc_mail_conn, DEMO_BASE64.ENCODE(UTL_RAW.CAST_TO_RAW(ls_passWord)));
    
    -- 組裝郵件標頭 (支援中文主旨)
    ls_subject := 'Subject: [' || UPPER(SYS_CONTEXT('USERENV', 'DB_NAME')) || '] - ' || as_subject;
    ls_msg_body := as_msg_body;
    
    -- 關鍵：寄件者/收件者必須包含 < > 符號，否則會報 permanent error
    UTL_SMTP.MAIL(lc_mail_conn, '<' || as_sender || '>');
    UTL_SMTP.RCPT(lc_mail_conn, '<' || as_recp || '>');
    
    -- 寫入郵件內容
    UTL_SMTP.OPEN_DATA(lc_mail_conn);
    ls_msg_body := 'From: ' || as_sender || CHR(13) || CHR(10) ||
                   'To: ' || as_recp || CHR(13) || CHR(10) ||
                   ls_subject || CHR(13) || CHR(10) || CHR(13) || CHR(10) ||
                   ls_msg_body;
    
    -- 使用 WRITE_RAW_DATA 支援中文主旨 (但內文仍需注意編碼)
    UTL_SMTP.WRITE_RAW_DATA(lc_mail_conn, UTL_RAW.CAST_TO_RAW(ls_msg_body));
    -- 若內文需支援中文，請改用 MIME multipart 格式或 BASE64 編碼內文
    
    UTL_SMTP.CLOSE_DATA(lc_mail_conn);
    UTL_SMTP.QUIT(lc_mail_conn);

EXCEPTION
    WHEN UTL_SMTP.INVALID_OPERATION THEN DBMS_OUTPUT.PUT_LINE('Invalid Operation');
    WHEN UTL_SMTP.TRANSIENT_ERROR  THEN DBMS_OUTPUT.PUT_LINE('Transient Error');
    WHEN UTL_SMTP.PERMANENT_ERROR  THEN DBMS_OUTPUT.PUT_LINE('Permanent Error');
    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Others: ' || SQLERRM);
END SEND_MAIL;
/

Base64 編碼套件 (DEMO_BASE64)

若編譯時提示 DEMO_BASE64.ENCODE must be declared，請建立以下套件：

CREATE OR REPLACE PACKAGE DEMO_BASE64 IS
    FUNCTION ENCODE (P_BIN IN RAW) RETURN VARCHAR2;
    FUNCTION DECODE (P_TXT IN VARCHAR2) RETURN RAW;
END DEMO_BASE64;
/

CREATE OR REPLACE PACKAGE BODY DEMO_BASE64 IS
    -- 實作 Base64 編解碼邏輯 (此處省略完整程式碼，請參考 Oracle 官方範例或 UTIL_BASE64)
    ...
END DEMO_BASE64;
/

實際呼叫測試

EXEC SEND_MAIL('sender@company.com', 'receiver@company.com', '測試主旨', 'This is a test mail from Oracle.');

常見問題與解決方案

1. 中文亂碼：主旨可用 WRITE_RAW_DATA + UTF-8 編碼；內文建議採用 MIME 格式並指定 Content-Type: text/plain; charset=utf-8。
2. 認證失敗：確認郵件伺服器支援 AUTH LOGIN，帳號密碼正確，且已 Base64 編碼。
3. 連線逾時：檢查防火牆、SELinux、雲端安全群組是否開放 25/465/587 埠。
4. Oracle 11g/12c/19c：建議改用 UTL_MAIL 或 APEX_MAIL 簡化開發，支援附件、HTML 格式、SSL/TLS。

進階應用建議

• 將發送邏輯封裝為非同步作業（DBMS_SCHEDULER / AQ 队列），避免阻塞主交易。
• 建立郵件發送日誌表，記錄狀態、重試次數、錯誤訊息，便於監控與追蹤。
• 生產環境請強制啟用 STARTTLS 或 SMTPS (465 埠)，保護帳號密碼傳輸安全。