從 SQL*Loader 控制檔一鍵生成 External Table 定義：Oracle 9i EXTERNAL_TABLE=GENERATE_ONLY 技巧

背景

External Table（9i 引入）可將平面檔映射為資料表，支援 SQL 查詢、並行、Join。但 DDL 語法與 SQL*Loader 控制檔不同，手寫易錯。

捷徑：SQL*Loader 幫你寫 DDL

sqlldr scott/tiger CONTROL=loademp.ctl EXTERNAL_TABLE=GENERATE_ONLY

• **不載入資料**，僅將控制檔轉譯為 External Table DDL，輸出至 Log 檔。
• 複製 Log 中的 `CREATE TABLE ... ORGANIZATION EXTERNAL ...` 區塊即可使用。

生成腳本結構解析

1. CREATE DIRECTORY：指向輸入檔所在 OS 目錄，需 `CREATE ANY DIRECTORY` 權限。建議改為有意義名稱（如 `EXT_TAB_DATA_DIR`）。
2. CREATE TABLE ... ORGANIZATION EXTERNAL：
   • `TYPE ORACLE_LOADER`（預設）。
   • `DEFAULT DIRECTORY` 引用上述 Directory 物件。
   • `ACCESS PARAMETERS`：記錄分隔符、欄位定義、日期格式、欄位映射（對應控制檔 `FIELDS TERMINATED BY`、`POSITION` 等）。
   • `LOCATION ('filename.dat')`：可多檔案、支援萬用字元。
   • `REJECT LIMIT UNLIMITED`：錯誤不中斷。
3. 範例 INSERT ... SELECT：示範如何從 External Table 寫入正式表。
4. 清理 DROP TABLE / DROP DIRECTORY。

實戰範例

控制檔 `loademp.ctl`：

LOAD DATA
INFILE 'emp.dat'
APPEND INTO TABLE emp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr, hiredate DATE 'DD-MON-YY', sal, comm, deptno)

執行 `sqlldr ... EXTERNAL_TABLE=GENERATE_ONLY` 後，Log 含：

CREATE DIRECTORY ext_tab_dir AS '/data/load';
CREATE TABLE emp_ext
( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9),
  mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_tab_dir
  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('emp.dat')
)
REJECT LIMIT UNLIMITED;

進階應用

• 將生成的 DDL 納入部署腳本，版控管理。
• 搭配 `DBMS_SCHEDULER` 定期 `INSERT /*+ APPEND */ INTO target SELECT * FROM ext_tab` 實現增量載入。
• 12c+ 可用 `ORACLE_DATAPUMP` 型別 External Table 實現資料庫間無 DB Link 的並行傳輸。