Oracle EBS 伺服器目錄物件配置與 UTL_FILE 實戰

Oracle EBS 伺服器目錄物件配置與 UTL_FILE 實戰

Oracle E-Business Suite (EBS) 常需與作業系統檔案系統互動（報表輸出、介面檔案交換、日誌記錄）。本文詳細說明 CREATE DIRECTORY 權限模型、OS 層面目錄建立與權限設定、UTL_FILE 讀寫實戰與常見錯誤排查。

架構圖解

+------------------+     CREATE DIRECTORY      +------------------+
|  Oracle Database |  ----------------------->  |  OS File System  |
|                  |  DIRECTORY Object         |                  |
|  UTL_FILE PKG    |  READ/WRITE GRANT         |  /mail_file/     |
|                  |  <----------------------   |  data/PROD/      |
+------------------+     OS Permissions        +------------------+
        ^                                                  ^
        |                                                  |
   DB User                                            oracle OS User

步驟 1：資料庫層面建立目錄物件

-- 必須具備 CREATE ANY DIRECTORY 權限 (通常由 SYS 執行)
CONN / AS SYSDBA

CREATE OR REPLACE DIRECTORY MAIL_FILE_DIR AS '/mail_file/data/PROD';

-- 授權給應用使用者或 PUBLIC (視安全策略而定)
GRANT READ, WRITE ON DIRECTORY MAIL_FILE_DIR TO APPS_USER;
-- 或
GRANT READ, WRITE ON DIRECTORY MAIL_FILE_DIR TO PUBLIC;

RAC 環境特別說明

雙節點環境下，兩個節點的資料庫皆需執行上述 SQL。目錄物件屬於資料庫字典物件，透過 Data Guard/閃回同步，但建議雙邊手動執行確保一致性。

步驟 2：作業系統層面建立目錄與權限

# 切換至 root 或 oracle 安裝擁有者
cd /

# 建立多層目錄
mkdir -p /mail_file/data/PROD

# 設定權限：確保 oracle OS 使用者可讀寫
chown oracle:oinstall /mail_file/data/PROD
chmod 775 /mail_file/data/PROD  # 建議 775，避免 775 安全風險

# 驗證
ls -ld /mail_file/data/PROD
# 輸出應為：drwxrwxr-x 2 oracle oinstall 4096 ... /mail_file/data/PROD

步驟 3：UTL_FILE 寫入檔案實戰

DECLARE
  fhandle  UTL_FILE.FILE_TYPE;
  v_dir    VARCHAR2(30) := 'MAIL_FILE_DIR';
  v_file   VARCHAR2(100) := 'example.txt';
BEGIN
  -- 開啟檔案寫入模式 (W = Write, 覆寫)
  fhandle := UTL_FILE.FOPEN(v_dir, v_file, 'W', 32767);  -- max_linesize 32KB
  
  UTL_FILE.PUT_LINE(fhandle, 'gh test write one');
  UTL_FILE.PUT_LINE(fhandle, 'gh test write two');
  UTL_FILE.PUT_LINE(fhandle, 'Timestamp: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
  
  -- 關閉檔案 (強制刷新緩衝區)
  UTL_FILE.FCLOSE(fhandle);
  
  DBMS_OUTPUT.PUT_LINE('File written successfully.');
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Invalid directory path or object.');
  WHEN UTL_FILE.INVALID_MODE THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Invalid open mode.');
  WHEN UTL_FILE.WRITE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Write failed (disk full/permissions).');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
    IF UTL_FILE.IS_OPEN(fhandle) THEN
      UTL_FILE.FCLOSE(fhandle);
    END IF;
END;
/

步驟 4：UTL_FILE 讀取檔案實戰

DECLARE
  fhandle   UTL_FILE.FILE_TYPE;
  v_buffer  VARCHAR2(4000);
  v_dir     VARCHAR2(30) := 'MAIL_FILE_DIR';
  v_file    VARCHAR2(100) := 'example.txt';
BEGIN
  fhandle := UTL_FILE.FOPEN(v_dir, v_file, 'R', 32767);
  
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(fhandle, v_buffer);
      DBMS_OUTPUT.PUT_LINE(v_buffer);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;  -- 讀取完畢
    END;
  END LOOP;
  
  UTL_FILE.FCLOSE(fhandle);
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Directory object not found or OS path invalid.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
    IF UTL_FILE.IS_OPEN(fhandle) THEN
      UTL_FILE.FCLOSE(fhandle);
    END IF;
END;
/

常見錯誤代碼對照表

進階最佳實踐

1. 使用 Database Directory 而非硬編碼路徑

-- 彈性配置：不同環境 (DEV/UAT/PROD) 僅需修改 Directory 物件路徑
-- 程式碼無需變更
SELECT directory_name, directory_path FROM dba_directories WHERE directory_name = 'MAIL_FILE_DIR';

2. 大檔案處理：分塊讀寫

-- 寫入大量資料時，定期 FFUSH 避免記憶體暴增
UTL_FILE.PUT_RAW(fhandle, UTL_RAW.CAST_TO_RAW(large_blob_chunk), TRUE); -- TRUE = auto flush
-- 或
UTL_FILE.FFLUSH(fhandle);

3. 安全性強化

• 目錄物件路徑嚴禁指向系統關鍵目錄 (/etc, /tmp, $ORACLE_HOME 等)
• 採用最小權限原則：僅授權給特定應用使用者，避免 GRANT TO PUBLIC
• 啟用稽核：AUDIT READ, WRITE ON DIRECTORY MAIL_FILE_DIR BY ACCESS;
• 定期清理暫存檔：排程刪除超過 N 天的介面檔案

4. 替代方案評估

UTL_FILE 仍是 PL/SQL 操作 OS 檔案的基石，但生產環境務必建立「目錄物件管理規範」：集中登記、定期審計、權限最小化、路徑隔離。EBS 環境更應配合 Apps 模組標準目錄結構 ($APPLCSF/$APPLLOG 等) 統一規劃。