Oracle 外部表導入日期時間類型：ACCESS PARAMETERS 日期格式化完整範例

外部表讓 Oracle 直接以 SQL 查詢 OS 平面檔，ACCESS PARAMETERS 中的日期格式化是導入時間類型資料的關鍵。本文完整示範建立目錄、定義外部表、指定日期格式 DATE "YYYY-MM-DD HH24:MI:SS"、查詢驗證全流程。

完整建立腳本

-- 1. 建立目錄物件 (需 CREATE ANY DIRECTORY 權限)
CREATE DIRECTORY extdir AS 'E:\app\extdir';  -- Windows 路徑
-- Linux: CREATE DIRECTORY extdir AS '/u01/app/extdir';

-- 2. 定義外部表
CREATE TABLE ext_test (
  rt DATE,           -- 日期時間欄位
  rtype CHAR(1)      -- 一般字元欄位
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY extdir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','           -- 欄位分隔符
    MISSING FIELD VALUES ARE NULL      -- 缺漏欄位視為 NULL
    (
      rt DATE "YYYY-MM-DD HH24:MI:SS", -- 關鍵：指定日期格式
      rtype CHAR(1)
    )
  )
  LOCATION ('test_ext.txt')
)
REJECT LIMIT UNLIMITED;  -- 允許所有錯誤行進入 BAD 檔，不中斷查詢

外部檔案範例 (test_ext.txt)

2012-09-01,D
2012-10-02,D
2012-10-19,D
2012-11-23,D
2012-11-20,D
2012-01-11,D
2012-06-12,D
2012-08-25,D
2012-05-07,D
2012-04-08,D

查詢驗證

SQL> SELECT * FROM ext_test;

RT                     R
------------------- ----
2012-09-01 00:00:00  D
2012-10-02 00:00:00  D
2012-10-19 00:00:00  D
2012-11-23 00:00:00  D
2012-11-20 00:00:00  D
2012-01-11 00:00:00  D
2012-06-12 00:00:00  D
2012-08-25 00:00:00  D
2012-05-07 00:00:00  D
2012-04-08 00:00:00  D

10 rows selected.

關鍵 ACCESS PARAMETERS 子句解析

日期格式化模式對照 (TO_DATE 相容)

YYYY-MM-DD HH24:MI:SS   -- 2012-09-01 14:30:00
DD-MON-YYYY             -- 01-SEP-2012
YYYYMMDD                -- 20120901
YYYY-MM-DD"T"HH24:MI:SS -- ISO 8601: 2012-09-01T14:30:00

常見錯誤與排查

• KUP-04063: field rt not found：欄位順序與定義不符、分隔符錯誤
• ORA-29913: error in executing ODCIEXTTABLEFETCH：日期格式不匹配，檢查 DATE "format" 與檔案實際內容
• ORA-29913: invalid directory：目錄物件路徑不存在或 Oracle 服務帳戶無讀取權限
• 查看 BAD/LOG 檔：SELECT * FROM USER_EXTERNAL_TABLES 確認位置，OS 層面查看內容

進階：直接載入正式表 (CTAS)

CREATE TABLE formal_table AS
SELECT rt, rtype FROM ext_test;
-- 或分批 INSERT /*+ APPEND */ INTO formal_table SELECT * FROM ext_test;

核心優勢：外部表將 ETL 的「抽取/轉換」合併為單一 SQL 查詢，無需 SQL*Loader 控制檔、無中間暫存表，維護極簡。