Oracle UTL_SMTP 發送郵件完整實戰代碼 (第三部分：路徑解析與目錄物件管理)

代碼定位

本文為完整郵件發送套件的**第三部分**，專注於**檔案路徑解析函數 GET_FILE** 與 **動態目錄物件管理 (CREATE_DIRECTORY / DROP_DIRECTORY)**，為附件讀取提供底層支援。

1. GET_FILE — 跨平台路徑解析

FUNCTION GET_FILE (P_FILE VARCHAR2, P_GET INT) RETURN VARCHAR2 IS
  -- P_GET = 1: 返回目錄路徑
  -- P_GET = 2: 返回檔案名稱
  L_FILE VARCHAR2(1000);
BEGIN
  IF INSTR(P_FILE, '\') > 0 THEN
    -- Windows 路徑 (反斜線分隔)
    IF P_GET = 1 THEN
      -- 取最後一個反斜線前的部分
      L_FILE := SUBSTR(P_FILE, 1, INSTR(P_FILE, '\', -1) - 1);
    ELSIF P_GET = 2 THEN
      -- 取最後一個反斜線後的部分
      L_FILE := SUBSTR(P_FILE, -(LENGTH(P_FILE) - INSTR(P_FILE, '\', -1)));
    END IF;
  ELSIF INSTR(P_FILE, '/') > 0 THEN
    -- Linux/Unix 路徑 (正斜線分隔)
    IF P_GET = 1 THEN
      L_FILE := SUBSTR(P_FILE, 1, INSTR(P_FILE, '/', -1) - 1);
    ELSIF P_GET = 2 THEN
      L_FILE := SUBSTR(P_FILE, -(LENGTH(P_FILE) - INSTR(P_FILE, '/', -1)));
    END IF;
  END IF;
  RETURN L_FILE;
END GET_FILE;

函數行為範例

關鍵技術點

• `INSTR(str, delim, -1)`：從字串**末尾**向前搜尋最後一個分隔符位置 (Oracle 10g+ 支援負數 position)
• `SUBSTR(str, -n)`：從字串**末尾**取 n 個字元
• 雙平台支援：自動偵測 `\` 或 `/` 分隔符
• 無分隔符保護：返回 NULL，上層邏輯需處理

2. DROP_DIRECTORY — 安全清理目錄物件

PROCEDURE DROP_DIRECTORY (P_DIRECTORY_NAME VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 'DROP DIRECTORY ' || P_DIRECTORY_NAME;
EXCEPTION
  WHEN OTHERS THEN
    NULL;  -- 物件不存在時忽略錯誤，繼續執行
END DROP_DIRECTORY;

設計意圖

• 附件處理前**先刪除同名目錄物件**，避免 `ORA-00955: name is already used by an existing object`
• 使用動態 SQL (`EXECUTE IMMEDIATE`) 因 DDL 不能直接在 PL/SQL 中執行
• 例外全捕獲並忽略：不存在是正常預期狀態，不應中斷流程

3. CREATE_DIRECTORY — 建立並授權目錄物件

PROCEDURE CREATE_DIRECTORY (P_DIRECTORY_NAME VARCHAR2, P_DIR VARCHAR2) IS
BEGIN
  -- 建立目錄物件映射到作業系統目錄
  EXECUTE IMMEDIATE 'CREATE DIRECTORY ' || P_DIRECTORY_NAME || ' AS ''' || P_DIR || '''';
  
  -- 授權 PUBLIC 讀寫 (確保 Oracle 進程可存取)
  EXECUTE IMMEDIATE 'GRANT READ, WRITE ON DIRECTORY ' || P_DIRECTORY_NAME || ' TO PUBLIC';
EXCEPTION
  WHEN OTHERS THEN
    RAISE;  -- 建立失敗屬嚴重錯誤，必須拋出
END CREATE_DIRECTORY;

關鍵設計決策

完整套件上下文關聯

-- 主程序宣告區
L_DIRECTORY_BASE_NAME VARCHAR2(100) := 'DIR_FOR_SEND_MAIL';

-- ATTACHMENT 程序中調用 (第四部分)
DROP_DIRECTORY(DT_NAME);  -- DT_NAME = 'DIR_FOR_SEND_MAIL1' 等
CREATE_DIRECTORY(DT_NAME, GET_FILE(FILENAME, 1));  -- 取得目錄路徑
L_FILENAME := GET_FILE(FILENAME, 2);               -- 取得純檔名

-- 第五部分二進制附件同樣使用
L_FIL := BFILENAME(DT_NAME, L_FILENAME);  -- 建立 BFILE 定位器

權限前置條件

-- 執行 CREATE/DROP DIRECTORY 需權限
GRANT CREATE ANY DIRECTORY TO ;
GRANT DROP ANY DIRECTORY TO ;

-- UTL_FILE/DBMS_LOB 存取目錄物件需權限 (CREATE_DIRECTORY 已授權 PUBLIC 解決)
-- 或精確授權：
GRANT READ, WRITE ON DIRECTORY DIR_FOR_SEND_MAIL1 TO ;

避坑指南

路徑解析與目錄物件管理是附件發送的基礎設施。動態建立/刪除、跨平台路徑支援、冪等性保證、精確授權，四大原則構建穩健底層。