Oracle SQL*Loader 完整使用指南：從入門到進階 LOB 匯入

SQL*Loader (sqlldr) 是 Oracle 官方提供的高速大量資料載入工具，支援多種平面檔案格式（CSV、固定寬度、分隔符號等）高效匯入資料庫。本文從最簡單的範例入手，逐步深入涵蓋四種載入模式、資料格式處理、日期/函數轉換、換行符內嵌、LOB 大物件匯入等實戰技巧，並附上 PL/SQL 替代方案。

一、最快上手：五分鐘完成第一次匯入

1. 目標資料表

CREATE TABLE ftest (
    id NUMBER,
    username VARCHAR2(20),
    password VARCHAR2(20),
    sj DATE
);

2. 準備資料檔 data.txt (空白分隔)

1 f f 2010-08-19
2 f1 f1 2010-08-19
3 f2 f2 2010-08-19
4 f3 f3 2010-08-19
5 f4 f4 2010-08-19

3. 撰寫控制檔 cont.ctl

LOAD DATA
INFILE 'c:\data.txt'
INSERT INTO TABLE ftest
FIELDS TERMINATED BY " "
(id, username, password, sj)

模式說明： INSERT（預設）要求目標表為空；APPEND 追加；REPLACE/TRUNCATE 先清空再載入。

4. 執行匯入

sqlldr userid=scott/tiger control=c:\cont.ctl data=c:\data.txt

5. 驗證結果

SELECT * FROM ftest;

二、控制檔進階寫法：內嵌資料 (BEGINDATA)

無需外部資料檔，直接在控制檔內包含資料：

LOAD DATA
INFILE *
INTO TABLE dept
FIELDS TERMINATED BY ','
(deptno, dname, loc)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia

三、四大載入模式對照

四、常見資料格式處理

1. 分隔符號資料 (CSV 等)

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-- 支援雙引號包裹、雙引號轉義 ("" 代表 ")
-- 範例：10,Sales,"Virginia,USA"
--       20,Accounting,"Va,""USA"""

2. 定寬固定位置資料 (POSITION)

-- 絕對位置
(deptno POSITION(1:2), dname POSITION(3:16), loc POSITION(17:29))
-- 相對位置 (* 代表上一欄結束處)
(deptno POSITION(1:2), dname POSITION(*:16), loc POSITION(*:29))
-- 指定長度 (CHAR(n))
(deptno POSITION(1) CHAR(2), dname POSITION(*) CHAR(14), loc POSITION(*) CHAR(13))

3. 日期轉換

last_updated DATE 'DD/MM/YYYY'
-- 掩碼格式同 TO_DATE，支援 NLS 參數
last_updated DATE 'DD-MON-YYYY' "NLS_DATE_LANGUAGE=AMERICAN"

4. SQL 函數轉換 (大寫、字串拼接等)

dname "UPPER(:dname)",
loc "UPPER(:loc)",
entire_line ":deptno || :dname || :loc || :last_updated"
-- 若欄位可能為空，需在 LOAD DATA 後加 TRAILING NULLCOLS

五、特殊換行符處理

欄位內嵌換行符解決方案

1. 替換法： 資料檔用特殊標記 (如 \n)，載入時 REPLACE(:col, '\\n', CHR(10))。
2. 記錄分隔符法： 使用 str 指定唯一分隔符 (如 |\r\n)，資料內可自由包含換行。

六、LOB 大物件匯入 (CLOB/BLOB/BFILE)

1. 內聯 LOB (資料直接在主檔案)

comments CHAR(1000000)  -- 覆蓋預設 255 字元限制
-- 搭配 str 分隔符避免換行符衝突

2. 外聯 LOBFILE (獨立檔案)

LOAD DATA
INFILE 'main.dat'
INTO TABLE lob_demo
(owner POSITION(17:25),
 time_stamp POSITION(44:55) DATE "Mon DD HH24:MI",
 filename POSITION(57:100),
 data LOBFILE(filename) TERMINATED BY EOF)
BEGINDATA
-rw-r--r-- 1 tkyte tkyte 1220342 Jun 17 15:26 classes12.zip

• LOBFILE(filename) 指向外部檔案路徑 (相對或絕對)。
• TERMINATED BY EOF 讀取整個檔案內容。

3. 物件型別欄位 (如 ORDSYS.ORDIMAGE)

image COLUMN OBJECT (
    source COLUMN OBJECT (
        localData LOBFILE(file_name) TERMINATED BY EOF
        NULLIF file_name = 'none'
    )
)

七、PL/SQL 替代方案：DBMS_LOB 載入

無需 sqlldr，純資料庫端即可完成 LOB 匯入：

CREATE OR REPLACE DIRECTORY dir1 AS 'D:\oracle';
-- 目錄名預設大寫，區分大小寫需用雙引號："dir2"

DECLARE
    l_clob CLOB;
    l_bfile BFILE;
BEGIN
    INSERT INTO demo (id, theclob) VALUES (1, EMPTY_CLOB())
    RETURNING theclob INTO l_clob;

    l_bfile := BFILENAME('DIR1', 'test.txt');
    DBMS_LOB.FILEOPEN(l_bfile);
    DBMS_LOB.LOADFROMFILE(l_clob, l_bfile, DBMS_LOB.GETLENGTH(l_bfile));
    DBMS_LOB.FILECLOSE(l_bfile);
    COMMIT;
END;
/

八、常見錯誤與對策速查

• ORA-01722 invalid number： 尾欄為 NUMBER 且資料檔為 Windows 換行 → 加 "str '\r\n'" 或 TERMINATED BY WHITESPACE。
• 中文亂碼： 控制檔加 CHARACTERSET 'ZHS16GBK' 或 'UTF8'。
• 欄位超過 255 字元： 明確指定 CHAR(n) 或 VARCHARC(n)。
• SQL*Loader-510 記錄過長： 檢查資料檔是否損毀 (FTP 傳輸中斷等)。
• ORA-01653 表空間無法擴展： 擴充表空間或增加資料檔。

掌握上述技巧，即可應對從簡單 CSV 匯入到複雜 LOB 遷移的各種企業級資料載入需求。