Oracle SQL*Loader 多表載入實戰：控制檔案配置與資料分流技巧

前言

Oracle SQL*Loader (sqlldr) 是高效能大量資料載入工具。當單一資料檔需依條件分流載入多張目標表時，透過控制檔的 INTO TABLE 子句與 WHEN 條件即可實現。本文以實戰範例完整示範流程。

環境準備

-- 建立測試表
CREATE TABLE test1 (
    a1 VARCHAR2(10),
    a2 VARCHAR2(10),
    a3 VARCHAR2(10)
);

CREATE TABLE test2 (
    b1 VARCHAR2(10),
    b2 VARCHAR2(10)
);

資料檔範例 (data.txt)

TYPE1|VAL1|VAL2|VAL3
TYPE2|DATA1|DATA2
TYPE1|VAL4|VAL5|VAL6
TYPE2|DATA3|DATA4

第一欄 TYPE1 載入 test1，TYPE2 載入 test2。

控制檔完整配置 (loader.ctl)

LOAD DATA
INFILE 'data.txt'
BADFILE 'loader.bad'
DISCARDFILE 'loader.dsc'
APPEND
INTO TABLE test1
WHEN (1:5) = 'TYPE1'
FIELDS TERMINATED BY '|'
(
    a1 POSITION(7:10) CHAR,
    a2 POSITION(12:15) CHAR,
    a3 POSITION(17:20) CHAR
)
INTO TABLE test2
WHEN (1:5) = 'TYPE2'
FIELDS TERMINATED BY '|'
(
    b1 POSITION(7:10) CHAR,
    b2 POSITION(12:15) CHAR
)

關鍵指令解析

執行命令

sqlldr userid=eygle/eygle control=loader.ctl log=loader.log

進階技巧

1. 同一資料載入多表（無條件分流）

移除 WHEN 子句，每個 INTO TABLE 皆會處理所有記錄，實現一對多複製。

2. 使用續接欄位處理變長記錄

FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
(
    col1 CHAR,
    col2 CHAR(100)  -- 固定長度
)

3. 載入前轉換函數

col3 "UPPER(:col3)",
col4 "TO_DATE(:col4, 'YYYYMMDD')"

4. 直接路徑載入 (DIRECT=TRUE)

繞過 SQL 層直接寫入資料區塊，大幅提升效能，但有限制（如不可有觸發器、外鍵、叢集索引等）。

常見錯誤與排查

• 欄位位置錯誤：檢查 POSITION 與實際資料對齊，建議先用 POSITION(*) 相對定位
• WHEN 條件不匹配：確認資料檔編碼、分隔符、位置偏移
• 記錄被導向 DISCARDFILE：表示無符合條件的 INTO TABLE，需檢查條件邏輯
• 權限不足：執行帳號需具備目標表 INSERT 權限及目錄物件讀寫權限

總結

SQL*Loader 透過單一控制檔的多 INTO TABLE + WHEN 機制，原生支援多表條件分流載入，無需外部腳本預處理。掌握控制檔語法細節，能高效完成異構資料遷移、ETL 落地等大量載入任務。