---

---

有多種方式可以將文字檔案的資料匯入到資料庫中，例如，利用PLSQL Developer軟體進行復制貼上，利用外部表，利用SQL*Loader等方式。至於EXCEL中的資料可以另存為csv檔案（csv檔案其實是逗號分隔的文字檔案），然後匯入到資料庫中。

下面簡單介紹一下SQL*Loader的使用方式。

SQL*Loader是一個Oracle工具，能夠將資料從外部資料檔案裝載到資料庫中。SQL*Loader必須包含一個控制檔案，該控制檔案是SQL*Loader的中樞核心，控制檔案能夠控制外部資料檔案中的資料如何對映到Oracle的表和列。通常與SPOOL匯出文字資料方法配合使用。SQL*Loader能夠接收多種不同格式的資料檔案。檔案可以儲存在磁碟或磁帶上，或記錄本身可以被巢狀到控制檔案中。記錄格式可以是定長的或變長的，定長記錄是指這樣的記錄：每條記錄具有相同的固定長度，並且每條記錄中的資料域也具有相同的固定長度、資料型別和位置。

SQL*Loader的資料匯入比較專業，有各種引數及選項可供選擇，經常是作為資料倉儲中大型資料的匯入方法選擇。

SQL*Loader的優點：

1、可將匯入命令寫入BAT檔案直接批量處理

2、匯入處理比較專業，提供各種引數選擇

3、無需操作Oracle所在伺服器

SQL*Loader也有缺點，例如，Excel檔案需要另存為txt或csv格式才能匯入到資料庫中。

總得來說這種方法是最值得采用的，可以自動建立作業系統的批處理檔案執行SQL*Loader命令，將資料匯入原始接收表，並在資料庫中設定觸發器進行精細操作。

SQL*Loader有兩種使用方法：

1、只使用一個控制檔案，在這個控制檔案中包含資料

2、使用一個控制檔案和一個資料檔案

下面給出SQL*Loader控制檔案的一個示例：

options(SKIP=1,errors=1000) --options(SKIP=1,ROWS=1000,errors=1000)

UNRECOVERABLE --不產生日誌

load data

--CHARACTERSET  utf8  --ZHS16GBK

LENGTH CHARACTER

infile 'E:\sql\sqlldr\test.csv'

APPEND into table zh_lhr --insert/APPEND/replace

fields terminated by ',' optionally enclosed by '"'  ---②terminated by x'09'   一個製表符（TAB）

trailing nullcols

(

position(*:16)  "TRIM(:COL_5)"   "SEQ.NEXTVAL"  date 'YYYY/MM/DD HH24:MI:SS',

rn  ,

IMIX_TAG  char(4000)  "trim(:IMIX_TAG)"

)

其中，CHARACTERSET指定檔案的編碼格式，infile指定匯入的檔案。

接下來就是執行匯入命令了，如下所示：

sqlldr  使用者名稱/使用者名稱密碼@資料庫名稱 control= 控制檔名.ctl  parallel=y  log='log.txt'  bad='bad.bad'  direct=true  readsize=4194304

當要載入的資料檔案比較大的時候該如何提高SQL*Loader的效能呢？可以從以下幾個方面考慮：

①　ROWS的預設值為64，可以根據需要指定更合適的ROWS引數來指定每次提交記錄數。

②　採用DIRECT=TRUE匯入可以跳過資料庫的相關邏輯，直接將資料匯入到資料檔案中，可以提高匯入資料的效能。

③　通過指定UNRECOVERABLE選項，可以寫少量的日誌，而從提高資料載入的效能。不過，推薦在載入完成後立即對資料庫或至少對表空間備份。

當載入大量資料時，最好抑制日誌的產生：

ALTER   TABLE   RESULTXT   NOLOGGING;

將表修改為NOLOGGING，可以只產生少量的Redo日誌，從而提高匯入效率。在CONTROL檔案中的load data前邊加一行：UNRECOVERABLE，此選項必須要與DIRECT共同使用。對於超大資料檔案的匯入就要用併發操作了，即同時執行多個匯入任務：

sqlldr   userid=/   control=result1.ctl   direct=true   parallel=true

下表給出在使用SQL*Loader的過程中，一些常用的需求實現方法：

下表給出了在使用SQL*Loader的過程中，經常會遇到的一些錯誤及其解決方法：

關於SQL*Loader還有很多引數本書不再詳述，具體可以參考官方文件。有關如何匯出資料到EXCEL中，本書也不再詳述。讀者若有需要可以關注作者的微信公眾號來閱讀。

真題1、Which two statements are true regarding the usage of the SQL*Loader utility? (Choose two.)

A、You can load data into multiple tables during the same load session.

B、You can load data from multiple files to a table during the same load session.

C、You cannot perform selective data loading based on the values available in the records.

D、You can use an export file generated by the EXPDP utility as an input data file to load the data.

答案：A、B。

題目中要求選出關於SQL*Loader工具的描述中正確的兩個選項。

本題中，對於選項A，可以在一個SQL*Loader會話中匯入多個表的資料，根據前面的分析，該功能可以實現。所以，選項A正確。

對於選項B，可以在一個SQL*Loader會話中將多個資料檔案匯入到1個表中，根據前面的分析，該功能可以實現。所以，選項B正確。

對於選項C，在匯入資料的時候可以使用WHEN關鍵詞實現選擇性的匯入。所以，選項C錯誤。

對於選項D，SQL*Loader是文字匯入工具，只能匯入文字資料，而exp匯出的檔案屬於二進位制檔案，所以不能匯入。所以，選項D錯誤。

所以，本題的答案為A、B。

真題2、Which statement is true about loading data using the conventional path of SQL*Loader?

A、Redo is not generated while performing conventional path loads.

B、Only PRIMARY KEY, UNIQUE KEY, and NOT NULL constraints are checked.

C、The SQL*Loader control file is a text file that contains data definition language (DDL) instructions.

D、Instead of performing transactions, SQL*Loader directly writes data blocks to the data files.

E、INSERT triggers are disabled before the conventional path load and reenabled at the end of the load.

答案：C。

題目要求選出關於SQL*Loader工具的常規路徑載入說法正確的選項。

本題中，對於選項A，常規路徑載入的時候會產生Redo日誌，選項A錯誤。

對於選項B，所有的約束均有效，而不止PRIMARY KEY、UNIQUE KEY和NOT NULL這3項約束。所以，選項B錯誤。

對於選項C，SQL*Loader的控制檔案是一個文字檔案，其中包含了一些DDL結構。所以，選項C正確。

對於選項D，SQL*Loader的常規路徑載入是經過記憶體的，而不是直接寫入資料檔案。所以，選項D錯誤。

對於選項E，SQL*Loader載入的時候，表上的相關觸發器是會執行的。所以，選項E錯誤。

所以，本題的答案為C。

---

---

1、 使用CONCATENATE合併多行記錄

---

---

sqlldr userid=lgone/tiger control=a.ctl 
其中，a.ctl為控制檔案，內容：
　　LOAD DATA －－控制檔案標識
　　INFILE 't.dat' －－ 要匯入的檔案 
　　INFILE 'tt.dat' －－ 匯入多個檔案 
　　INFILE * －－ "*" 代表要匯入的內容就在control檔案裡，下面的BEGINDATA後面就是匯入的內容 
　　INSERT －－資料載入方式，有四種
INTO TABLE table_name －－ 指定裝入的表
　　BADFILE 'c:bad.txt' －－指定壞檔案地址
LOGFILE 'c:log.txt' －－log檔案
DISCARD 'c:discard.txt' －－包含丟棄資料的檔案
ERRORS=1000 －－指定錯誤的數目

可以使用sqlldr檢視幫助。

[@more@]

以下是4種裝入表的方式： 
　　 APPEND 原先的表有資料，就加在後面 
　　 INSERT 裝載空表 如果原先的表有資料 sqlloader會停止，預設值 
　　 REPLACE 原先的表有資料，原先的資料會全部刪除 
　　 TRUNCATE 指定的內容和replace的相同 會用truncate語句刪除現存資料 
　　
指定的TERMINATED可以在表的開頭，也可在表的內部欄位部分： 
　　FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' －－OPTIONALLY ENCLOSED BY是指欄位值用什麼括起來，平時我們都是用單引號，如'value'
　　 裝載這種資料: 10,lg,"""lg""","lg,lg" 
　　 在表中結果: 10 lg "lg" lg,lg 
　　 TERMINATED BY X'09' －－ 表示欄位之間通過製表符tab鍵（以十六進位制格式 '09' 表示 ）分割，也可以把FIELDS TERMINATED BY ';'分號表示成ascii編碼
　　 TERMINATED BY WRITESPACE －－ 以空白分割，裝載這種資料: 10 lg lg

　　 (col_1 [interger external] TERMINATED BY ',' , 
　　 col_2 [date "dd-mon-yyy"] TERMINATED BY ',' , 
　　 col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg' )

當某列為空，不存在對應值：　　
　　 TRAILING NULLCOLS －－如果某個欄位沒有對應的值時（空格、空白、或null），被當作null列，向表中插入null 
　　
不匯入某列： 
　　( col_1 , col_2 ,col_filler FILLER －－FILLER 關鍵字（不是filter）指定此列（第三列）的數值不會被裝載，如: lg,lg,not，結果 lg lg) 
也可以寫成這樣：( col_1 , col_2 ) －－資料檔案中存在超過兩列資料，但是隻匯入前兩列
　　FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
　　(DEPTNO, DNAME, LOC ) 
　　BEGINDATA 
　　10,Sales,"""USA""" 
　　20,Accounting,"Virginia,USA" 
　　30,Consulting,Virginia 
　　40,Finance,Virginia 
　　50,"Finance","",Virginia －－loc 列將為空 
　　60,"Finance",,Virginia －－loc 列將為空

跳過資料行，可以用 "SKIP n" 關鍵字來指定匯入時可以跳過多少行資料。如：
INTO TABLE load_positional_data 
SKIP 5 
　　　
當沒宣告FIELDS TERMINATED BY ','用位置告訴欄位裝載資料： 
　　 ( 
　　 col_1 position(1:2), －－1到2，(01) 代表第一個字元
　　 col_2 position(3:10), －－3到10
　　 col_3 position(*:16), －－ 這個欄位的開始位置在前一欄位的結束位置 
　　 col_4 position(1:16), 
　　 col_5 position(3:10) char(8) －－ 指定欄位的型別 
　　 ) 
　　
　　BEGINDATA －－ 對應開始的 INFILE * 要匯入的內容就在control檔案裡，注意begindata後的數值前面不能有空格
　　10,Sql,what 
　　20,lg,show 
　　
使用函式（包括自定義函式）、日期的某種表達方式： 
　　FIELDS TERMINATED BY ',' 
　　(DEPTNO, 
　　DNAME "upper(:dname)", －－ 使用函式 
　　LOC "upper(:loc)", 
　　LAST_UPDATED date 'dd/mm/yyyy', －－或'dd-month-yyyy'等，也可以使用自定義函式LAST_UPDATED "my_to_date( :last_updated )" ， my_to_date()為自定義函式
　　ENTIRE_LINE ":deptno||:dname||:loc||:last_updated" 
　　)

載入有換行符的資料： 
　　注意: unix 和 windows換行符不同 n & /n 
(......
　　COMMENTS "replace(:comments,'n',chr(10))" --replace 的使用幫助轉換換行符 
......
)

載入每行的行號: 
　　......
　　( seqno RECNUM －－載入每行的行號 
　　text Position(1:20)) 
　　BEGINDATA 
　　fsdfasj －－自動分配一行號給載入 表t 的seqno欄位, 此行行號為1 
　　fasdjfasdfl －－此行行號為2 
　　
合併多行記錄為一行記錄: 
　CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record. 
CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.

LOAD DATA 
　　INFILE * 
　　concatenate 3 －－通過關鍵字concatenate 把幾行的記錄看成一行記錄 
　　INTO TABLE DEPT 
......
　　(DEPTNO, 
　　DNAME "upper(:dname)", 
　　LOC "upper(:loc)", 
　　LAST_UPDATED date 'dd/mm/yyyy' 
　　) 
　　BEGINDATA 
　　10,Sales, －－其實這3行看成一行 10,Sales,Virginia,1/5/2000 
　　Virginia, 
　　1/5/2000 
　　
這列子用 continueif list="," 也可以,告訴sqlldr在每行的末尾找逗號 找到逗號就把下一行附加到上一行.
　　
　　LOAD DATA 
　　INFILE * 
　　continueif this(1:1) = '-' -- 找每行的開始是否有連線字元'-',有就把下一行連線為一行: 
　　 如 -10,Sales,Virginia, 
　　 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000 
　　其中1:1 表示從第一行開始 並在第一行結束 還有continueif next, 但continueif list最理想

將資料匯入多個表： 
LOAD DATA 
INFILE * 
REPLACE 
INTO TABLE emp 
WHEN empno != ' ' 
( empno POSITION(1:4) INTEGER EXTERNAL, 
ename POSITION(6:15) CHAR, 
deptno POSITION(17:18) CHAR, 
mgr POSITION(20:23) INTEGER EXTERNAL 
) 
INTO TABLE proj 
WHEN projno != ' ' 
( projno POSITION(25:27) INTEGER EXTERNAL, 
empno POSITION(1:4) INTEGER EXTERNAL 
)

匯入資料時修改資料： 
在匯入資料到資料庫時，可以修改資料。注意，這僅適合於常規匯入，並不適合 direct匯入方式. 
LOAD DATA 
INFILE * 
INTO TABLE modified_data 
( rec_no "my_db_sequence.nextval", 
region CONSTANT '31', 
time_loaded "to_char(SYSDATE, 'HH24:MI')", 
data1 POSITION(1:5) ":data1/100", 
data2 POSITION(6:15) "upper(:data2)", 
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')" 
) 
BEGINDATA 
11111AAAAAAAAAA991201 
22222BBBBBBBBBB990112

LOAD DATA 
INFILE 'mail_orders.txt' 
BADFILE 'bad_orders.txt' 
APPEND 
INTO TABLE mailing_list 
FIELDS TERMINATED BY "," 
( addr, 
city, 
state, 
zipcode, 
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)", 
mailing_city "decode(:mailing_city, null, :city, :mailing_city)", 
mailing_state 
)

SQL*Loader 資料的提交：

一般情況下是在匯入資料檔案資料後提交的。也可以通過指定 ROWS= 引數來指定每次提交記錄數。

提高 SQL*Loader　的效能： 
1) 一個簡單而容易忽略的問題是，沒有對匯入的表使用任何索引和/或約束(主鍵)。如果這樣做，甚至在使用ROWS=引數時，會很明顯降低資料庫匯入效能。 
2) 可以新增 DIRECT=TRUE來提高匯入資料的效能。當然，在很多情況下，不能使用此引數。 
3) 通過指定　UNRECOVERABLE選項，可以關閉資料庫的日誌。這個選項只能和 direct 一起使用。 
4) 可以同時執行多個匯入任務.

常規匯入與direct匯入方式的區別： 
常規匯入可以通過使用 INSERT語句來匯入資料。Direct匯入可以跳過資料庫的相關邏輯(DIRECT=TRUE)，而直接將資料匯入到資料檔案中。

1、普通裝載  LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  (DEPTNO, DNAME, LOC ) BEGINDATA 10,Sales,"""USA""" 20,Accounting,"Virginia,USA"  30,Consulting,Virginia 40,Finance,Virginia 50,"Finance","",Virginia --loc 列將為空 60,"Finance",,Virginia   --loc 列將為空 注：BEGINDATA後的數值前面不能有空格   2、TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情況  LOAD DATA  INFILE *  INTO TABLE DEPT  REPLACE  FIELDS TERMINATED BY WHITESPACE  --FIELDS TERMINATED BY x'20'  (DEPTNO,   DNAME,   LOC  )  BEGINDATA  10 Sales Virginia   注：x'20'表示字元ASCII碼的16進位制數值    3、指定不裝載那一列  LOAD DATA  INFILE *  INTO TABLE DEPT  REPLACE  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  (DEPTNO,   FILLER_1 FILLER, --下面的 "Something Not To Be Loaded" 將不會被裝載   DNAME,   LOC  )  BEGINDATA  20,Something Not To Be Loaded,Accounting,"Virginia,USA"   4、position的列子  LOAD DATA  INFILE *  INTO TABLE DEPT  REPLACE  (DEPTNO position(1:2),   DNAME position(*:16), --這個欄位的開始位置在前一欄位的結束位置   LOC position(*:29),   ENTIRE_LINE position(1:29)  )  BEGINDATA  10Accounting Virginia,USA   結果：10 | Accounting Vir | ginia,USA | 10Accounting Virginia,USA   5、使用函式日期的一種表達TRAILING NULLCOLS的使用  LOAD DATA  INFILE *  INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS --這句的意思是將沒有對應值的列都置為null --如果第一行改為 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了  (DEPTNO,   DNAME "upper(:dname)", --使用函式   LOC "upper(:loc)",   LAST_UPDATED date 'dd/mm/yyyy', --日期的一種表達方式。還有'dd-mon-yyyy'等 ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"  )  BEGINDATA  10,Sales,Virginia,1/5/2000  20,Accounting,Virginia,21/6/1999  30,Consulting,Virginia,5/1/2000  40,Finance,Virginia,15/3/2001   注：可以通過:dname型別呼叫函式，特別注意date函式的使用。   6、合併多行記錄為一行記錄 LOAD DATA  INFILE *  concatenate 3 --通過關鍵字concatenate 把幾行的記錄看成一行記錄  INTO TABLE DEPT  replace  FIELDS TERMINATED BY ','  (DEPTNO,   DNAME "upper(:dname)",   LOC "upper(:loc)",   LAST_UPDATED date 'dd/mm/yyyy'  )  BEGINDATA  10,Sales, Virginia, 1/5/2000 注：例如有些文字檔案以N行為一迴圈記錄資料，則可以這樣匯入。     7、使用continueif來合併記錄行   上例可直接使用continueif last= ','來告訴Oracle如果前一個資料以','結尾，則這個附加到上一行   LOAD DATA  INFILE *  continueif last= ',' INTO TABLE DEPT  replace  FIELDS TERMINATED BY ','  (DEPTNO,   DNAME "upper(:dname)",   LOC "upper(:loc)",   LAST_UPDATED date 'dd/mm/yyyy'  )  BEGINDATA  10,Sales, Virginia, 1/5/2000   注：ContinueIf還可以使用this或next選項，具體操作見文件 8、載入每行的行號   LOAD DATA  INFILE *  INTO TABLE DEPT replace  (DEPTNORECNUM //載入每行的行號   ENTIRE_LINEPosition(1:1024) )  BEGINDATA  fsdfasj     --自動分配行號到DEPTNO欄位，此行為1  fasdjfasdfl --自動遞增，此行為2 9、載入有換行符的資料 ①使用一個非換行符的字元 LOAD DATA  INFILE *  INTO TABLE DEPT  REPLACE  FIELDS TERMINATED BY ','  TRAILING NULLCOLS  (DEPTNO,  DNAME "upper(:dname)",  LOC "upper(:loc)",  LAST_UPDATED "my_to_date( :last_updated )",  COMMENTS "replace(:comments,'%%',chr(10))" --用replace函式轉換成換行符  )  BEGINDATA  10,Sales,Virginia,01-april-2001,This is the Sales%%Office in Virginia  20,Accounting,Virginia,13/04/2001,This is the Accounting%%Office in Virginia  30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting%%Office in Virginia  40,Finance,Virginia,987268297,This is the Finance%%Office in Virginia   注：換行的特殊字元如果使用'\n'，則會在Windows編譯過程中直接換成換行符，導致無法轉換   ②使用fix屬性 Load DATA  INFILE demo1.dat "fix 68"  INTO TABLE t1_a  REPLACE  FIELDS TERMINATED BY ','  TRAILING NULLCOLS  (DEPTNO,  DNAME "upper(:dname)",  LOC "upper(:loc)",  LAST_UPDATED Date 'dd/mm/yyyy', ENTIRE_LINE  ) demo1.dat  10,aaaab,Virginia,01/05/2001,This is the aaaab Office in Virginia 20,aaaac,Virginia,13/04/2001,This is the aaaac Office in Virginia 30,aaaad,Virginia,14/04/2001,This is the aaaad Office in Virginia 40,aaaae,Virginia,16/02/2001,This is the aaaae Office in Virginia   注：fix只能加在外部檔案資料匯入時，另外需要每行資料長度都相等。    --下面這種方法也一樣 Load DATA  INFILE demo17.dat "fix 70"  INTO TABLE t1_a  REPLACE  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  TRAILING NULLCOLS  (DEPTNO,   DNAME "upper(:dname)",   LOC "upper(:loc)",   LAST_UPDATED Date 'dd/mm/yyyy', ENTIRE_LINE  ) demo2.dat  10,aaaab,Virginia,01/05/2001,"This is the aaaab Office in Virginia" 20,aaaac,Virginia,13/04/2001,"This is the aaaac Office in Virginia" 30,aaaad,Virginia,14/04/2001,"This is the aaaad Office in Virginia" 40,aaaae,Virginia,16/02/2001,"This is the aaaae Office in Virginia"  ③ 使用var屬性 Load DATA  INFILE demo17.dat "var 3"  INTO TABLE t1_a  REPLACE  FIELDS TERMINATED BY ','  TRAILING NULLCOLS  (DEPTNO,   DNAME "upper(:dname)",   LOC "upper(:loc)",   LAST_UPDATED Date 'dd/mm/yyyy', ENTIRE_LINE  ) demo17.dat  03510,Sales,Virginia,01/01/2001,This 03920,Accounting,Virginia,13/04/2001,Thi 04530,Consulting,Virginia,14/04/2001,This is t 07140,Finance,Virginia,14/04/2001,This is the Finance Office in Virginia 注：var 3 表示前三位用於說明該條記錄的長度 (但是誰告訴我長度怎麼數的？-_-|||)   ④使用str屬性    可使用str來定義一個行結尾符   計算以|\r\n 結束的值：  select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;  結果 7C0D0A Load DATA  INFILE demo17.dat "str X'7C0D0A'"  INTO TABLE t1_a  REPLACE  FIELDS TERMINATED BY ','  TRAILING NULLCOLS  (DEPTNO,   DNAME "upper(:dname)",   LOC "upper(:loc)",   LAST_UPDATED Date 'dd/mm/yyyy', ENTIRE_LINE  )  demo17.dat  10,Sales,Virginia,01/01/2001,This is the Sales  Office in Virginia| 20,Accounting,Virginia,13/04/2001,This is the Accounting  Office in Virginia| 30,Consulting,Virginia,14/04/2001,This is the Consulting  Office in Virginia| 40,Finance,Virginia,14/04/2002,This is the Finance  Office in Virginia   注意：同樣需要在外部檔案資料匯入中使用，且最後一個不用加；另外注意不要有空格   10、nullif匯入   LOAD DATA INFILE * INTO TABLE t1_a REPLACE (DEPTNO position(1:2) integer external nullif DEPTNO='1', --當匯入deotno的值為'1'時，則該條記錄不匯入 DNAME position(3:8) ) BEGINDATA 1 10 20lg   注：需要注意的是在前面指定的資料型別以及後面的引號！

---

---

---

---

Oracle 的SQL*LOADER可以將外部資料載入到資料庫表中。下面是SQL*LOADER的基本特點：
1）能裝入不同資料型別檔案及多個資料檔案的資料
2）可裝入固定格式，自由定界以及可度長格式的資料
3）可以裝入二進位制，壓縮十進位制資料
4）一次可對多個表裝入資料
5）連線多個物理記錄裝到一個記錄中
6）對一單記錄分解再裝入到表中
7）可以用 數對制定列生成唯一的KEY
8）可對磁碟或 磁帶資料檔案裝入製表中
9）提供裝入錯誤報告
10）可以將檔案中的整型字串，自動轉成壓縮十進位制並裝入列表中。
1.2控制檔案
控制檔案是用一種語言寫的文字檔案，這個文字檔案能被SQL*LOADER識別。SQL*LOADER根據控制檔案可以找到需要載入的資料。並且分析和解釋這些資料。控制檔案由三個部分組成：
l 全域性選件，行，跳過的記錄數等；
l INFILE子句指定的輸入資料；
l 資料特性說明。
1.3輸入檔案
對於 SQL*Loader, 除控制檔案外就是輸入資料。SQL*Loader可從一個或多個指定的檔案中讀出資料。如果 資料是在控制檔案中指定，就要在控制檔案中寫成 INFILE * 格式。當資料固定的格式（長度一樣）時且是在檔案中得到時，要用INFILE "fix n"
load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,
當資料是可變格式（長度不一樣）時且是在檔案中得到時，要用INFILE "var n"。如：
load data
infile 'example.dat' "var 3"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
009hello,cd,010world,im,
012my,name is,
1.4壞檔案
壞檔案包含那些被SQL*Loader拒絕的記錄。被拒絕的記錄可能是不符合要求的記錄。
壞檔案的名字由 SQL*Loader命令的BADFILE 引數來給定。
1.5日誌檔案及日誌資訊
當SQL*Loader 開始執行後，它就自動建立 日誌檔案。日誌檔案包含有載入的總結，載入中的錯誤資訊等。
控制檔案語法
控制檔案的格式如下：
OPTIONS （ { [SKIP=integer] [ LOAD = integer ]
[ERRORS = integer] [ROWS=integer]
[BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] )
LOAD[DATA]
[ { INFILE | INDDN } {file | * }
[STREAM | RECORD | FIXED length [BLOCKSIZE size]|
VARIABLE [length] ]
[ { BADFILE | BADDN } file ]
{DISCARDS | DISCARDMAX} integr ]
[ {INDDN | INFILE} . . . ]
[ APPEND | REPLACE | INSERT ]
[RECLENT integer]
[ { CONCATENATE integer |
CONTINUEIF { [THIS | NEXT] (start[: end])LAST }
Operator { 'string' | X 'hex' } } ]
INTO TABLE [user.]table
[APPEND | REPLACE|INSERT]
[WHEN condition [AND condition]...]
[FIELDS [delimiter] ]
(
column {
RECNUM | CONSTANT value |
SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |
[POSITION ( { start [end] | * [ + integer] }
) ]
datatype
[TERMINATED [ BY ] {WHITESPACE| [X] 'character' } ]
[ [OPTIONALLY] ENCLOSE[BY] [X]'charcter']
[NULLIF condition ]
[DEFAULTIF condotion]
}
[ ,...]
)
[INTO TABLE...]
[BEGINDATA]
1）要載入的資料檔案：
1．INFILE 和INDDN是同義詞，它們後面都是要載入的資料檔案。如果用 * 則表示資料就在控制檔案內。在INFILE 後可以跟幾個檔案。
2．STRAM 表示一次讀一個位元組的資料。新行代表新物理記錄（邏輯記錄可由幾個物理記錄組成）。
3．RECORD 使用宿主作業系統檔案及記錄管理系統。如果資料在控制檔案中則使用這種方法。
3． FIXED length 要讀的記錄長度為length位元組，
4． VARIABLE 被讀的記錄中前兩個位元組包含的長度，length 記錄可能的長度。缺傷為8k位元組。
5． BADFILE和BADDN同義。Oracle 不能載入資料到資料庫的那些記錄。
6． DISCARDFILE和DISCARDDN是同義詞。記錄沒有通過的資料。
7． DISCARDS和DISCARDMAX是同義詞。Integer 為最大放棄的檔案個數。
2）載入的方法：
1．APPEND 給表新增行。
2．INSERT 給空表增加行（如果表中有記錄則退出）。
3．REPLACE 先清空表在載入資料。
4． RECLEN 用於兩種情況，1）SQLLDR不能自動計算記錄長度，2）或使用者想看壞檔案的完整記錄時。對於後一種，Oracle只能按常規把壞記錄部分寫到錯誤的地方。如果看整條記錄，則可以將整條記錄寫到壞檔案中。
3）指定最大的記錄長度：
1． CONCATENATE 允許使用者設定一個整數，表示要組合邏輯記錄的數目。
4）建立邏輯記錄：
1．THIS 檢查當前記錄條件，如果為真則連線下一個記錄。
2．NEXT 檢查下一個記錄條件。如果為真，則連線下一個記錄到當前記錄來。
2． Start: end 表示要檢查在THIS或NEXT字串是否存在繼續串的列，以確定是否進行連線。如：continueif next(1-3)='WAG' 或continueif next(1-3)=X'0d03if'
5）指定要載入的表：


1．INTO TABLE 要加的表名。
2．WHEN 和select WHERE類似。用來檢查記錄的情況，如：when(3-5)='SSM' and (22)='*"
6）介紹並括起記錄中的欄位：
1． FIELDS給出記錄中欄位的分隔符，FIELDS格式為：
FIELDS [TERMIALED [BY] {WHITESPACE | [X] 'charcter'} ]
[ [ OPTIONALLY] ENCLOSE [BY] [X]'charcter' ]
TERMINATED 讀完前一個欄位即開始讀下一個欄位直到介紹。
WHITESPACE 是指結束符是空格的意思。包括空格、Tab、換行符、換頁符及回車符。如果是要判斷但字元，可以用單引號括起，如X'1B'等。
OPTIONALLY ENCLOSED 表示資料應由特殊字元括起來。也可以括在TERMINATED字元內。使用OPTIONALLY要同時用TERMINLATED。
ENCLOSED 指兩個分界符內的資料。如果同時用 ENCLOSED和TERMINAED ，則它們的順序決定計算的順序。
7）定義列：
column 是表列名。列的取值可以是：
BECHUM 表示邏輯記錄數。第一個記錄為1,第2個記錄為2。
CONSTANT 表示賦予常數。
SEQUENCE 表示序列可以從任意序號開始，格式為：
SEQUENCE （ { integer | MAX |COUNT} [,increment]
POSITION 給出列在邏輯記錄中的位置。可以是絕對的，或相對前一列的值。格式為：
POSITION ( {start[end] | * [+integer] } )
Start 開始位置
* 表示前欄位之後立刻開始。
+ 從前列開始向後條的位置數。
8）定義資料型別：
可以定義14種資料型別：
CHAR
DATE
DECIMAL EXTERNAL
DECIMAL
DOUBLE
FLOAT
FLOAT EXTERNAL
GRAPHIC EXTERNAL
INTEGER
INTEGER EXTERNAL
SMALLINT
VARCHAR
VARGRAPHIC
1．字元型別資料
CHAR[ (length)] [delimiter]
length預設為 1.
2.日期型別資料
DATE [ ( length)]['date_format' [delimiter]
使用to_date函式來限制。
3．字元格式中的十進位制
DECIMAL EXTERNAL [(length)] [delimiter]
用於常規格式的十進位制數（不是二進位制=> 一個位等於一個bit）。
4．壓縮十進位制格式資料
DECIMAL （digtial [,precision]）
5．雙精度符點二進位制
DOUBLE
6．普通符點二進位制
FLOAT
7．字元格式符點數
FLOAT EXTERNAL [ (length) ] [delimiter]
8．雙位元組字串資料
GRAPHIC [ (legth)]
9．雙位元組字串資料
GRAPHIC EXTERNAL[ (legth)]
10．常規全字二進位制整數
INTEGER
11．字元格式整數
INTEGER EXTERNAL
12．常規全字二進位制資料
SMALLINT
13．可變長度字串
VARCHAR
14．可變雙位元組字串資料
VARGRAPHIC


2.2寫控制檔案CTL
1． 各資料檔案的檔名;
2．各資料檔案格式;
3．各資料檔案裡各資料記錄欄位的屬性；
4．接受資料的ORACLE表列的屬性；
5．資料定義；
6．其它
資料檔案的要求：
資料型別的指定
CHAR 字元型
INTEGER EXTERNAL 整型
DECIMAL EXTERNAL 浮點型
3.1資料檔案的內容
可以在OS下的一個檔案；或跟在控制檔案下的具體資料。資料檔案可以是：
1、 二進位制與字元格式：LOADER可以把二進位制檔案讀（當成字元讀）列表中
2、 固定格式：記錄中的資料、資料型別、 資料長度固定。
3、 可變格式：每個記錄至少有一個可變長資料欄位，一個記錄可以是一個連續的字串。
資料段的分界（如姓名、年齡）如用“，”作欄位的 分 ；用，"’作資料
括號等
4、 LOADER可以使用多個連續欄位的物理記錄組成一個邏輯記錄，記錄檔案執行情況檔案：包括以下內容：
1、 執行日期：軟體版本號
2、 全部輸入，輸出檔名；對命令列的展示資訊，補充資訊，
3、 對每個裝入資訊報告：如表名，裝入情況；對初始裝入， 加截入或更新裝
入的選擇情況，欄資訊
4、 資料錯誤報告：錯誤碼；放棄記錄報告
5、 每個裝X報告：裝入行；裝入行數，可能跳過行數；可能拒絕行數；可能放
棄行數等
6、 統計概要：使用空間（包大小，長度）；讀入記錄數，裝入記錄數，跳過記
錄數；拒絕記錄數，放棄記錄數；執行時間等。
==========================================================================================================
sql load的一點小總結
sqlldr userid=lgone/tiger control=a.ctl 
LOAD DATA 
INFILE 't.dat' // 要匯入的檔案 
// INFILE 'tt.date' // 匯入多個檔案 
// INFILE * // 要匯入的內容就在control檔案裡 下面的BEGINDATA後面就是匯入的內容
INTO TABLE table_name // 指定裝入的表 
BADFILE 'c:bad.txt' // 指定壞檔案地址
************* 以下是4種裝入表的方式 
APPEND // 原先的表有資料 就加在後面 
// INSERT // 裝載空表 如果原先的表有資料 sqlloader會停止 預設值 
// REPLACE // 原先的表有資料 原先的資料會全部刪除 
// TRUNCATE // 指定的內容和replace的相同 會用truncate語句刪除現存資料
************* 指定的TERMINATED可以在表的開頭 也可在表的內部欄位部分 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
// 裝載這種資料: 10,lg,"""lg""","lg,lg" 
// 在表中結果: 10 lg "lg" lg,lg 
// TERMINATED BY X '09' // 以十六進位制格式 '09' 表示的 
// TERMINATED BY WRITESPACE // 裝載這種資料: 10 lg lg
TRAILING NULLCOLS ************* 表的欄位沒有對應的值時允許為空
************* 下面是表的欄位 
( 
col_1 , col_2 ,col_filler FILLER // FILLER 關鍵字 此列的數值不會被裝載 
// 如: lg,lg,not 結果 lg lg 
) 
// 當沒宣告FIELDS TERMINATED BY ',' 時 
// ( 
// col_1 [interger external] TERMINATED BY ',' , 
// col_2 [date "dd-mon-yyy"] TERMINATED BY ',' , 
// col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg' 
// ) 
// 當沒宣告FIELDS TERMINATED BY ','用位置告訴欄位裝載資料 
// ( 
// col_1 position(1:2), 
// col_2 position(3:10), 
// col_3 position(*:16), // 這個欄位的開始位置在前一欄位的結束位置 
// col_4 position(1:16), 
// col_5 position(3:10) char(8) // 指定欄位的型別 
// )
BEGINDATA // 對應開始的 INFILE * 要匯入的內容就在control檔案裡 
10,Sql,what 
20,lg,show
===================================================================================== 
//////////// 注意begindata後的數值前面不能有空格
1 ***** 普通裝載 
LOAD DATA 
INFILE * 
INTO TABLE DEPT 
REPLACE 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
(DEPTNO, 
DNAME, 
LOC 
) 
BEGINDATA 
10,Sales,"""USA""" 
20,Accounting,"Virginia,USA" 
30,Consulting,Virginia 
40,Finance,Virginia 
50,"Finance","",Virginia // loc 列將為空 
60,"Finance",,Virginia // loc 列將為空
2 ***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情況 
LOAD DATA 
INFILE * 
INTO TABLE DEPT 
REPLACE 
FIELDS TERMINATED BY WHITESPACE 
-- FIELDS TERMINATED BY x'09' 
(DEPTNO, 
DNAME, 
LOC 
) 
BEGINDATA 
10 Sales Virginia
3 ***** 指定不裝載那一列 
LOAD DATA 
INFILE * 
INTO TABLE DEPT 
REPLACE 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
( DEPTNO, 
FILLER_1 FILLER, // 下面的 "Something Not To Be Loaded" 將不會被裝載 
DNAME, 
LOC 
) 
BEGINDATA 
20,Something Not To Be Loaded,Accounting,"Virginia,USA"
4 ***** position的列子 
LOAD DATA 
INFILE * 
INTO TABLE DEPT 
REPLACE 
( DEPTNO position(1:2), 
DNAME position(*:16), // 這個欄位的開始位置在前一欄位的結束位置 
LOC position(*:29), 
ENTIRE_LINE position(1:29) 
) 
BEGINDATA 
10Accounting Virginia,USA
5 ***** 使用函式 日期的一種表達 TRAILING NULLCOLS的使用 
LOAD DATA 
INFILE * 
INTO TABLE DEPT 
REPLACE 
FIELDS TERMINATED BY ',' 
TRAILING NULLCOLS // 其實下面的ENTIRE_LINE在BEGINDATA後面的資料中是沒有直接對應 
// 的列的值的 如果第一行改為 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了 
(DEPTNO, 
DNAME "upper(:dname)", // 使用函式 
LOC "upper(:loc)", 
LAST_UPDATED date 'dd/mm/yyyy', // 日期的一種表達方式 還有'dd-mon-yyyy' 等 
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated" 
) 
BEGINDATA 
10,Sales,Virginia,1/5/2000 
20,Accounting,Virginia,21/6/1999 
30,Consulting,Virginia,5/1/2000 
40,Finance,Virginia,15/3/2001
6 ***** 使用自定義的函式 // 解決的時間問題 
create or replace 
function my_to_date( p_string in varchar2 ) return date 
as 
type fmtArray is table of varchar2(25);
l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy', 
'dd/mm/yyyy', 
'dd/mm/yyyy hh24:mi:ss' ); 
l_return date; 
begin 
for i in 1 .. l_fmts.count 
loop 
begin 
l_return := to_date( p_string, l_fmts(i) ); 
exception 
when others then null; 
end; 
EXIT when l_return is not null; 
end loop;
if ( l_return is null ) 
then 
l_return := 
new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 * 
p_string, 'GMT', 'EST' ); 
end if;
return l_return; 
end; 
/
LOAD DATA 
INFILE * 
INTO TABLE DEPT 
REPLACE 
FIELDS TERMINATED BY ',' 
TRAILING NULLCOLS 
(DEPTNO, 
DNAME "upper(:dname)", 
LOC "upper(:loc)", 
LAST_UPDATED "my_to_date( :last_updated )" // 使用自定義的函式 
) 
BEGINDATA 
10,Sales,Virginia,01-april-2001 
20,Accounting,Virginia,13/04/2001 
30,Consulting,Virginia,14/04/2001 12:02:02 
40,Finance,Virginia,987268297 
50,Finance,Virginia,02-apr-2001 
60,Finance,Virginia,Not a date
7 ***** 合併多行記錄為一行記錄 
LOAD DATA 
INFILE * 
concatenate 3 // 通過關鍵字concatenate 把幾行的記錄看成一行記錄 
INTO TABLE DEPT 
replace 
FIELDS TERMINATED BY ',' 
(DEPTNO, 
DNAME "upper(:dname)", 
LOC "upper(:loc)", 
LAST_UPDATED date 'dd/mm/yyyy' 
) 
BEGINDATA 
10,Sales, // 其實這3行看成一行 10,Sales,Virginia,1/5/2000 
Virginia, 
1/5/2000 
// 這列子用 continueif list="," 也可以 
告訴sqlldr在每行的末尾找逗號 找到逗號就把下一行附加到上一行
LOAD DATA 
INFILE * 
continueif this(1:1) = '-' // 找每行的開始是否有連線字元 - 有就把下一行連線為一行 
// 如 -10,Sales,Virginia, 
// 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000 
// 其中1:1 表示從第一行開始 並在第一行結束 還有continueif next 但continueif list最理想 
INTO TABLE DEPT 
replace 
FIELDS TERMINATED BY ',' 
(DEPTNO, 
DNAME "upper(:dname)", 
LOC "upper(:loc)", 
LAST_UPDATED date 'dd/mm/yyyy' 
) 
BEGINDATA // 但是好象不能象右面的那樣使用 
-10,Sales,Virginia, -10,Sales,Virginia, 
1/5/2000 1/5/2000 
-40, 40,Finance,Virginia,13/04/2001 
Finance,Virginia,13/04/2001
8 ***** 載入每行的行號
load data 
infile * 
into table t 
replace 
( seqno RECNUM //載入每行的行號 
text Position(1:1024)) 
BEGINDATA 
fsdfasj //自動分配一行號給載入 表t 的seqno欄位 此行為 1 
fasdjfasdfl // 此行為 2 ...
9 ***** 載入有換行符的資料 
注意: unix 和 windows 不同 n & /n 
< 1 > 使用一個非換行符的字元 
LOAD DATA 
INFILE * 
INTO TABLE DEPT 
REPLACE 
FIELDS TERMINATED BY ',' 
TRAILING NULLCOLS 
(DEPTNO, 
DNAME "upper(:dname)", 
LOC "upper(:loc)", 
LAST_UPDATED "my_to_date( :last_updated )", 
COMMENTS "replace(:comments,'n',chr(10))" // replace 的使用幫助轉換換行符 
) 
BEGINDATA 
10,Sales,Virginia,01-april-2001,This is the SalesnOffice in Virginia 
20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia 
30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia 
40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia
< 2 > 使用fix屬性 
LOAD DATA 
INFILE demo17.dat "fix 101" 
INTO TABLE DEPT 
REPLACE 
FIELDS TERMINATED BY ',' 
TRAILING NULLCOLS 
(DEPTNO, 
DNAME "upper(:dname)", 
LOC "upper(:loc)", 
LAST_UPDATED "my_to_date( :last_updated )", 
COMMENTS 
) 
demo17.dat 
10,Sales,Virginia,01-april-2001,This is the Sales 
Office in Virginia 
20,Accounting,Virginia,13/04/2001,This is the Accounting 
Office in Virginia 
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting 
Office in Virginia 
40,Finance,Virginia,987268297,This is the Finance 
Office in Virginia
// 這樣裝載會把換行符裝入資料庫 下面的方法就不會 但要求資料的格式不同
LOAD DATA 
INFILE demo18.dat "fix 101" 
INTO TABLE DEPT 
REPLACE 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS 
(DEPTNO, 
DNAME "upper(:dname)", 
LOC "upper(:loc)", 
LAST_UPDATED "my_to_date( :last_updated )", 
COMMENTS 
) 
demo18.dat 
10,Sales,Virginia,01-april-2001,"This is the Sales 
Office in Virginia" 
20,Accounting,Virginia,13/04/2001,"This is the Accounting 
Office in Virginia" 
30,Consulting,Virginia,14/04/2001 12:02:02,"This is the Consulting 
Office in Virginia" 
40,Finance,Virginia,987268297,"This is the Finance 
Office in Virginia"
< 3 > 使用var屬性 
LOAD DATA 
INFILE demo19.dat "var 3" 
// 3 告訴每個記錄的前3個位元組表示記錄的長度 如第一個記錄的 071 表示此記錄有 71 個位元組 
INTO TABLE DEPT 
REPLACE 
FIELDS TERMINATED BY ',' 
TRAILING NULLCOLS 
(DEPTNO, 
DNAME "upper(:dname)", 
LOC "upper(:loc)", 
LAST_UPDATED "my_to_date( :last_updated )", 
COMMENTS 
) 
demo19.dat 
07110,Sales,Virginia,01-april-2001,This is the Sales 
Office in Virginia 
07820,Accounting,Virginia,13/04/2001,This is the Accounting 
Office in Virginia 
08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting 
Office in Virginia 
07140,Finance,Virginia,987268297,This is the Finance 
Office in Virginia
< 4 > 使用str屬性 
// 最靈活的一中 可定義一個新的行結尾符 win 回車換行 : chr(13)||chr(10)
此列中記錄是以 a|rn 結束的 
select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual; 
結果 7C0D0A
LOAD DATA 
INFILE demo20.dat "str X'7C0D0A'" 
INTO TABLE DEPT 
REPLACE 
FIELDS TERMINATED BY ',' 
TRAILING NULLCOLS 
(DEPTNO, 
DNAME "upper(:dname)", 
LOC "upper(:loc)", 
LAST_UPDATED "my_to_date( :last_updated )", 
COMMENTS 
) 
demo20.dat 
10,Sales,Virginia,01-april-2001,This is the Sales 
Office in Virginia| 
20,Accounting,Virginia,13/04/2001,This is the Accounting 
Office in Virginia| 
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting 
Office in Virginia| 
40,Finance,Virginia,987268297,This is the Finance 
Office in Virginia|
============================================================================== 
象這樣的資料 用 nullif 子句
10-jan-200002350Flipper seemed unusually hungry today. 
10510-jan-200009945Spread over three meals.
id position(1:3) nullif id=blanks // 這裡可以是blanks 或者別的表示式 
// 下面是另一個列子 第一行的 1 在資料庫中將成為 null 
LOAD DATA 
INFILE * 
INTO TABLE T 
REPLACE 
(n position(1:2) integer external nullif n='1', 
v position(3:8) 
) 
BEGINDATA 
1 10 
20lg 
------------------------------------------------------------
如果是英文的日誌 格式，可能需要修改環境變數 nls_lang or nls_date_format
==========================================================================================================
Oracle SQL*Loader 使用指南（轉載）
SQL*Loader是Oracle資料庫匯入外部資料的一個工具.它和DB2的Load工具相似,但有更多的選擇，它支援變化的載入模式，可選的載入及多表載入.
如何使用 SQL*Loader 工具
我們可以用Oracle的sqlldr工具來匯入資料。例如:
sqlldr scott/tiger control=loader.ctl
控制檔案(loader.ctl) 將載入一個外部資料檔案（含分隔符). loader.ctl如下:
load data
infile 'c:datamydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )
mydata.csv 如下:
10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
下面是一個指定記錄長度的示例控制檔案。"*" 代表資料檔案與此檔案同名，即在後面使用BEGINDATA段來標識資料。
load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
Unloader這樣的工具
Oracle 沒有提供將資料匯出到一個檔案的工具。但是，我們可以用SQL*Plus的select 及 format 資料來輸出到一個檔案：
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
from tab1
where col2 = 'XYZ';
spool off
另外，也可以使用使用 UTL_FILE PL/SQL 包處理:
rem Remember to update initSID.ora, utl_file_dir='c:oradata' parameter
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %sn', 'TextField', 55);
utl_file.fclose(fp);
end;
/
當然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。
載入可變長度或指定長度的記錄
如：
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
下面是匯入固定位置（固定長度）資料示例：
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
跳過資料行：
可以用 "SKIP n" 關鍵字來指定匯入時可以跳過多少行資料。如：
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 5
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
匯入資料時修改資料：
在匯入資料到資料庫時，可以修改資料。注意，這僅適合於常規匯入，並不適合 direct匯入方式.如：
LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)
將資料匯入多個表：
如:
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)
匯入選定的記錄：
如下例： (01) 代表第一個字元, (30:37) 代表30到37之間的字元:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
匯入時跳過某些欄位：
可用 POSTION(x:y) 來分隔資料. 在Oracle8i中可以通過指定 FILLER 欄位實現。FILLER 欄位用來跳過、忽略匯入資料檔案中的欄位.如：
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)
匯入多行記錄：
可以使用下面兩個選項之一來實現將多行資料匯入為一個記錄:
CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.
SQL*Loader 資料的提交：
一般情況下是在匯入資料檔案資料後提交的。
也可以通過指定 ROWS= 引數來指定每次提交記錄數。
提高 SQL*Loader 的效能：
1) 一個簡單而容易忽略的問題是，沒有對匯入的表使用任何索引和/或約束(主鍵)。如果這樣做，甚至在使用ROWS=引數時，會很明顯降低資料庫匯入效能。
2) 可以新增 DIRECT=TRUE來提高匯入資料的效能。當然，在很多情況下，不能使用此引數。
3) 通過指定 UNRECOVERABLE選項，可以關閉資料庫的日誌。這個選項只能和 direct 一起使用。
4) 可以同時執行多個匯入任務.
常規匯入與direct匯入方式的區別：
常規匯入可以通過使用 INSERT語句來匯入資料。Direct匯入可以跳過資料庫的相關邏輯(DIRECT=TRUE)，而直接將資料匯入到資料檔案中。

---

---

 Oracle SQL*Loader使用案例

SQL*Loader是oracle提供的可以從多種平面檔案中向資料庫中載入資料的工具，它比較適合業務分析型別資料庫（資料倉儲）;使用sqlldr工具可以在很短的時間內向資料庫中載入大量的資料，像把製作好的excel表格匯入資料庫，可以說非常方便，相關的資料載入和解除安裝工具還有外部表，IMP/EXP，資料泵等，其實,關於SQL*Loader的學習多數時間是花在了琢磨sqlldr控制檔案的寫法上，下面來總結一下SQL*Loader學習過程和一些實驗案例。

一、sqlldr的命令幫助資訊

[oracle@wjq ~]$ sqlldr

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 11:46:27 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Usage: SQLLDR keyword=value [,keyword=value,...]

 

Valid Keywords:

 

    userid -- ORACLE username/password           #Oracle使用者名稱和口令

   control -- control file name                  #控制檔名

       log -- log file name                      #日誌檔名

       bad -- bad file name                      #錯誤檔名

      data -- data file name                     #資料檔名

   discard -- discard file name                  #廢棄檔名

discardmax -- number of discards to allow (Default all)        #允許廢棄的全部數目(預設全部)

      skip -- number of logical records to skip(Default 0)     #要跳過的邏輯記錄數目(預設0)

      load -- number of logical records to load(Default all)   #要載入的邏輯記錄數目(預設全部)

    errors -- number of errors to allow (Default 50)           #允許錯誤的數目（預設50）

      rows -- number of rows in conventional path bind array or between direct path data saves

               (Default: Conventional path 64, Direct path all)#常規路徑繫結陣列中或直接路徑儲存資料間的行數 (常規路徑預設64,直接路徑預設全部)

  bindsize -- size of conventional path bind array in bytes  (Default 256000)#常規路徑繫結資料的大小(預設256000位元組)

    silent -- suppress messages during run (header,feedback,errors,discards,partitions)#執行過程中隱藏的資訊(標題，反饋，錯誤，廢棄，分割槽)

    direct -- use direct path                      (Default FALSE)#使用直接路徑(預設FALSE)

   parfile -- parameter file: name of file that contains parameter specifications#引數檔案，包括引數說明檔案的名稱

  parallel -- do parallel load                     (Default FALSE)#執行並行載入(預設FALSE)

      file -- file to allocate extents from      #要從以下檔案中分配區的檔案

skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (Default FALSE)#不允許/允許使用無用的索引(預設FALSE)

skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default FALSE)#不維護索引，對受到影響的索引標記為失效(預設FALSE)

commit_discontinued -- commit loaded rows when load is discontinued  (Default FALSE)#提交載入中斷時已載入的行(預設FALSE)

  readsize -- size of read buffer                  (Default 1048576)#讀取緩衝區的大小(預設1048576位元組)

external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)#使用外部表進行載入:NOT_USED, GENERATE_ONLY, EXECUTE(預設NOT_USED)

columnarrayrows -- number of rows for direct path column array  (Default 5000)#直接路徑列陣列的行數(預設5000)

streamsize -- size of direct path stream buffer in bytes  (Default 256000)#直接路徑流緩衝區的大小(預設256000)

multithreading -- use multithreading in direct path  #在直接路徑中使用多執行緒

 resumable -- enable or disable resumable for current session  (Default FALSE)#啟用或禁用當前可恢復會話(預設FALSE)

resumable_name -- text string to help identify resumable statement#有助於標識可恢復語句的文字字串

resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)#RESUMABLE的等待時間(預設7200秒)

date_cache -- size (in entries) of date conversion cache  (Default 1000)#日期轉換高速緩衝區大小（以條目計）(預設1000條)

no_index_errors -- abort load on any index errors  (Default FALSE)

 

PLEASE NOTE: Command-line parameters may be specified either by

position or by keywords.  An example of the former case is 'sqlldr

scott/tiger foo'; an example of the latter is 'sqlldr control=foo

userid=scott/tiger'.  One may specify parameters by position before

but not after parameters specified by keywords.  For example,

'sqlldr scott/tiger control=foo logfile=log' is allowed, but

'sqlldr scott/tiger control=foo log' is not, even though the

position of the parameter 'log' is correct.

 

注意：

SQLLDR的引數組合比較靈活，即可以直接寫值，也可以寫關鍵字=值。例如：sqlldr scott/tiger foo 和 sqlldr control=foo userid=scott/tiger 兩種寫法均有效。

 

二、使用案例

1.1簡單例子

新建一個wjq_test1.ctl的控制檔案，控制檔案的名稱和檔案型別可以任意指定，接著在控制檔案中寫入內容

SCOTT@seiang11g>create table tb_loader as select * from bonus;

Table created.

 

SCOTT@seiang11g>desc tb_loader

 Name                             Null?    Type

 -------------------------------- -------- ------------------------------------

 ENAME                                     VARCHAR2(10)

 JOB                                       VARCHAR2(9)

 SAL                                       NUMBER

 COMM                                      NUMBER

 

控制檔案內容如下：

[oracle@wjq SQL*Loader]$ vim wjq_test1.ctl
LOAD DATA
INFILE *
INTO TABLE tb_loader
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891  
WARD,SALESMAN,3128
KING,PRESIDENT,2523

 

執行sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test1.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 14:43:12 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

 

可以發現提示已經生成了4條資料，接著連線資料庫檢視一下內容

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLEAK           3904
ALLEN      SALESMAN        2891
WARD       SALESMAN        3128
KING       PRESIDENT       2523

 

發現查詢到的內容就是控制檔案中BEGINDATA中的資料，資料已經被成功載入。

提示：要插入的表必須在資料庫中已經存在，再使用sqlldr向其中載入資料

 

2、SQL*Loader的體系分析

LOAD DATA

------------------------------------

INFILE *

INTO TABLE tb_loader

FIELDS TERMINATED BY ","

(ENAME,JOB,SAL)

BEGINDATA

------------------------------------

SMITH,CLEAK,3904

ALLEN,SALESMAN,2891 

WARD,SALESMAN,3128

KING,PRESIDENT,2523

 

 

1.2控制檔案解析

 

①第一部分：

LOAD DATA是標準語法，控制檔案一般都以此開頭，LOAD DATA前還可指定 UNRECOVERABLE或RECOVERABLE來控制此次載入的資料是否可恢復，或者指定CONTINUE_LOAD，表示繼續載入，控制檔案的其他語句可以檢視官方文件。

 

②中間部分：
*INFILE:表示資料檔案位置，如果值為*，表示資料就在控制檔案中，本例中沒有單獨的資料檔案，對於大多數載入而言，都會將資料檔案與控制檔案分離。
*INTO TABLE tbl_name: tbl_name即資料要載入到的目標表，該表在你執行sqlldr命令之前必須已經建立。
*INTO前還有一些很有意思的引數需要說明：
  *INSERT:向表中插入資料，表必須為空，如果表非空的話，執行sqlldr命令時會報錯，預設就是INSERT引數。
  *APPEND:向表中追加資料，不管表中是否有資料。
  *REPLACE:替換表中資料，相當於先DELETE表中全部資料,然後再INSERT。
  *TRUNCATE:類似REPLACE,只不過這裡不是用DELETE方式刪除表中資料，而是通過TRUNCATE的方式刪除，然後再INSERT。
*FIELDS TERMINATED BY "，"：設定資料部分字串的分隔值，這裡設定為逗 號（,）分隔，當然也可以換成其他任意可見字元，只要確定那是資料行中的分隔符就行。
*(ENAME,JOB,SAL):要插入的表的列名，這裡需要注意的是列名要與表中列名完全相同，列的順序可以與表中列順序不同，但是必須與資料部分的列一一對應?
*BEGINDATA:表示以下為待載入資料，僅當INFILE指定為*時有效。

③資料部分
在該案例中，是將資料部分與控制部分都放在控制檔案中，通常這部分是獨立存在於一個文字檔案中。如果是獨立的資料檔案，只需要將控制檔案中INFILE引數後面的*改為資料檔案的檔名即可。

 

1.3日誌檔案解析

在預設情況下，sqlldr命令在執行過程中，會自動產生一個與控制檔案同名的日誌檔案，副檔名為.log,日誌檔案中記錄了載入過程中的各項統計資訊，如一些初始化引數、讀取的記錄數、成功載入的記錄數、載入用時等。
前例中，執行完sqlldr命令之後，相同路徑下應該生成了一個ldr_case1.log檔案，直接以“記事本”工具開啟檢視，應該顯示如下內容：

[oracle@wjq SQL*Loader]$ cat wjq_test1.log 

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 14:43:12 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   /u01/app/oracle/SQL*Loader/wjq_test1.ctl
Data File:      /u01/app/oracle/SQL*Loader/wjq_test1.ctl
  Bad File:     /u01/app/oracle/SQL*Loader/wjq_test1.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TB_LOADER, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ENAME                               FIRST     *   ,       CHARACTER            
JOB                                  NEXT     *   ,       CHARACTER            
SAL                                  NEXT     *   ,       CHARACTER            


Table TB_LOADER:
  4 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             4
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Tue Oct 31 14:43:12 2017
Run ended on Tue Oct 31 14:43:12 2017

Elapsed time was:     00:00:00.03
CPU time was:         00:00:00.01

 

日誌檔案結構簡單，前面都是初始化的引數，中間及後半部分才是我們應該關注的，包括記錄的結構、操作的記錄數（含成功的和錯誤的）、花費的時間等，如在這個日誌檔案中加粗的部分顯示己經成功載入了 4條，共費時近40毫秒。

1.4錯誤檔案解析

sqlldr命令在執行過程中，不僅會產生日誌檔案，如果載入資料過程中由於資料不符合規範導致載入錯誤，還會產生一個同名的錯誤檔案，副檔名為bad(如果DBA不 顯式指定的話）。該檔案中記錄了出錯的資料。錯誤檔案中資料的格式與資料檔案完全相同，因此如果發現載入時出現錯誤檔案，根據日誌檔案分析出錯原因，解決後修改控制檔案中infile引數為錯誤檔案，然後重新執行sqlldr命令即可。

1.5廢棄檔案解析

除了日誌檔案和錯誤檔案，執行sqlldr命令時還有可能生成一個同名的廢棄檔案,副檔名為.dsc，在預設情況下不會有，必須在執行sqlldr命令時顯式指定廢棄檔案，並確實存在不符合匯入邏輯的記錄，裡面記錄了未被插入的資料
      上一篇文章中，介紹了SQL*Loader的使用方法及簡單的案例 Oracle SQL*Loader使用案例（一）,本篇將根據實際的使用案例來更深層次的來解讀SQL*Loader

目錄

 

SQL*Loader對不同檔案及格式的處理方法

 

2.1 Excel檔案

一般的Excel檔案最大行數不超過65536行，說明資料處理量並不大，處理Excel的方式是將其另存為CSV格式檔案,然後即可按照正常方式匯入即可。

2.2要載入的檔案不是以逗號分隔

有兩種方式可以參考：
1)修改資料檔案，將分隔符替換為逗號。
2)修改控制檔案，將FIELDS TERMINATED BY的值修改為實際的分隔符。

2.3要載入的資料中包含分隔符

例如,要向scott.tb_loader表插入資料提供的資料格式如下：
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523
    修改控制檔案,注意下列示例程式碼中的粗體字元,OPTIONALLY ENCLOSED BY引數指明定界符為雙引號(CSV格式檔案預設定界符就是雙引號,你可以根據實際情況修改OPTIONALLY的引數值),如下所示：

 

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test2.ctl 
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test2.dat'
TRUNCATE INTO TABLE tb_loader
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'  
(ENAME,JOB,SAL)

  

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test2.dat 
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523sqlldr

 

執行如上程式碼，並查詢結果如下：

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test2.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 14:56:40 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

  

--查詢結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLEAK           3904
ALLEN      SALER,M         2891
WARD       SALER,"S"       3128
KING       PRESIDENT       2523

 

2.4資料檔案沒有分隔符

如下的資料檔案專業叫做定長字串，sqlldr中處理定長字串也輕而易舉。針對此例，我們將控制檔案修改如下：

 

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test3.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat'
TRUNCATE INTO TABLE tb_loader
(
 ENAME position(1:5),
 JOB position(10:18),
 SAL position(23:26)
)

  

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test3.dat
SMITH    CLEAK        3904
ALLEN    SALESMAN     2891
WARD     SALESMAN     3128
KING     PRESIDENT    252

 

position關鍵字用來指定列的開始和結束位置，如JOB position(10:18)是指從第10個字元開始截止到第18個字元作為ENAME列的列值。position的寫法也很靈活，要實現上述功能還可以換成下列幾種形式：

①position(*+2:18):直接指定數值的方式叫作絕對偏移量，如果使用*號，專業名詞叫相對偏移量，表示上一個欄位從哪裡結束，這次就從哪裡開始，相對偏移量也 可以再做運算，比如Position(*+2:15)就表示從上次結束的位置+2的地方開始。

②position(*) char(9):這種相對偏移量+型別和長度的優勢在於，你只需要為第一列 指定開始位置，其他列只需要指定列長度就可以了，實際使用中比較省事。

sqlldr執行如上程式碼，並查詢結果如下：

 

--sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test3.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:04:13 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

  

--查詢結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLEAK           3904
ALLEN      SALESMAN        2891
WARD       SALESMAN        3128
KING       PRESIDENT        252

 

 

2.5資料檔案中的列比要匯入的表中的列少

在前面幾個例子中，數檔案中的列比表中的列要少的環境中演示的，這說明列少不怕，關鍵是看控制檔案中的配置。但是如果缺少的列必須賦值又怎麼辦呢？只需稍改下控制檔案即可，直接指定COMM列，並賦初始值0(這裡仍然引用ldr_case3.dat中的資料）：

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test4.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat' 
TRUNCATE INTO TABLE tb_loader
(
 ENAME position(1:5),
 JOB position(10:18),
 SAL position(23:26),
 COMM "0"
)

 

--sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test4.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:08:50 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

  

--檢視結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLEAK           3904          0
ALLEN      SALESMAN        2891          0
WARD       SALESMAN        3128          0
KING       PRESIDENT        252          0

 

COMM的值也可以根據其他列的值而定，修改控制檔案如下

 

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test5.ctl 
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat' 
TRUNCATE INTO TABLE tb_loader
(
 ENAME position(1:5),
 JOB position(10:18),
 SAL position(23:26),
 COMM "substr(:SAL,1,1)"
)

 

sqlldr執行上述程式碼,結果如下，很明顯發現COMM的值是根據SAL的值的第1位數字獲得

 

--sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test5.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:12:00 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

  

--執行結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLEAK           3904          3
ALLEN      SALESMAN        2891          2
WARD       SALESMAN        3128          3
KING       PRESIDENT        252          2

 

這裡COMM列的值根據SAL列值而定，我們通過一個SQL中的函式substr取SAL值的第一列，賦予COMM列，當然這只是一個示例，DBA可以根據實際需求進行適當的 修改，通過SQL中的函式可以實現很多很有意思的轉換，也許能夠為你省下很大力氣，而且如果現有函式無法實現，甚至可以通過PL/SQL編寫自定義的函式，然後在sqlldr的 控制檔案中呼叫，呼叫方式與系統自帶函式方式完全相同，這樣就可以根據需求對要載入 的列做審靈活的處理。

 

 

2.6資料檔案中的列比要匯入的表中列多

如果資料檔案中的列比要匯入的表中的列少，處理的時候可能麻煩些，多了反倒更簡單，針對不同情況，一般有以下兩種處理方式：
方式一：修改資料檔案，將多餘的資料刪除，不過以這種方式處理，小資料量時還可行，一旦資料檔案較大，幾百甚至上千兆，修改資料檔案耗時耗力。

方式二：使用sqlldr中控制檔案FILLER來排除不需要的列
1）演示資料檔案如下

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test6.dat
SMITH    7369   CLERK      1020   20  
ALLEN    7499   SALESMAN   1930   30  
WARD     7521   SALESMAN   1580   30  
JONES    7566   MANAGER    3195   20  
MARTIN   7654   SALESMAN   1580   30  
BLAKE    7698   MANAGER    3180   30  
CLARK    7782   MANAGER    2172   10  
SCOTT    7788   ANALYST    3220   20  
KING     7839   PRESIDENT  4722   10  
TURNER   7844   SALESMAN   1830   30  
ADAMS    7876   CLERK      1320   20  
JAMES    7900   CLERK      1280   30  
FORD     7902   ANALYST    3220   20  
MILLER   7934   CLERK      1022   10

此時我們的需求希望我們匯入第1、3、4列而跳過2、5列，建立控制檔案如下

 

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test6.ctl 
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test6.dat' 
TRUNCATE INTO TABLE tb_loader
(
 ENAME position(1:6),
 COL1 FILLER position(10:13),
 JOB position(17:25),
 SAL position(28:31)
)

sqlldr的控制檔案中對列定義時支援FILLER關鍵字，可以用來指定過濾列，在上述控制檔案中，我們就使用該關鍵字來過濾列，相當於第10到第13列之間的資料不匯入。
事實上由於此處為定長字串，我們在控制檔案中指定的position引數，己經限定了讀取的內容，你甚至可以刪除控制檔案中TCOL FILLER position (10:13)那行。

執行sqlldr命令： 

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test6.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:24:36 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 14

  

--查詢結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLERK           1020
ALLEN      SALESMAN        1930
WARD       SALESMAN        1580
JONES      MANAGER         3195
MARTIN     SALESMAN        1580
BLAKE      MANAGER         3180
CLARK      MANAGER         2172
SCOTT      ANALYST         3220
KING       PRESIDENT       4722
TURNER     SALESMAN        1830
ADAMS      CLERK           1320
JAMES      CLERK           1280
FORD       ANALYST         3220
MILLER     CLERK           1022

 

 

 

2）如果資料檔案中字串不是定長格式，而是通過分隔符來處理的，那控制檔案中就需要注意，如資料檔案如下：

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test7.dat
SMITH,7369,CLERK,1020,20  
ALLEN,7499,SALESMAN,1930,30  
WARD,7521,SALESMAN,1580,30  
JONES,7566,MANAGER,3195,20  
MARTIN,7654,SALESMAN,1580,30  
BLAKE,7698,MANAGER,3180,30  
CLARK,7782,MANAGER,2172,10  
SCOTT,7788,ANALYST,3220,20  
KING,7839,PRESIDENT,4722,10  
TURNER,7844,SALESMAN,1830,30  
ADAMS,7876,CLERK,1320,20  
JAMES,7900,CLERK,1280,30  
FORD,7902,ANALYST,3220,20  
MILLER,7934,CLERK,1022,10

此時建立控制檔案時，控制檔案中就必須制定FILLER，不然列中的值可能不對應，建立控制檔案如下

 

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test7.ctl 
LOAD DATA  
INFILE '/u01/app/oracle/SQL*Loader/wjq_test7.dat' 
TRUNCATE INTO TABLE tb_loader 
FIELDS TERMINATED BY "," 
(  
 ENAME,COL1 FILLER,JOB,SAL
) 

 

執行sqlldr命令，並檢視結果

 

--sqlldr命令  
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test7.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:32:48 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 14

  

--檢視結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLERK           1020
ALLEN      SALESMAN        1930
WARD       SALESMAN        1580
JONES      MANAGER         3195
MARTIN     SALESMAN        1580
BLAKE      MANAGER         3180
CLARK      MANAGER         2172
SCOTT      ANALYST         3220
KING       PRESIDENT       4722
TURNER     SALESMAN        1830
ADAMS      CLERK           1320
JAMES      CLERK           1280
FORD       ANALYST         3220
MILLER     CLERK           1022

      上兩篇文章中，介紹了SQL*Loader的使用方法及使用案例,本篇將根據實際的使用案例來更深層次的來解讀SQL*Loader
      Oracle SQL*Loader使用案例（一）
      Oracle SQL*Loader使用案例（二）

目錄

SQL*Loader對不同檔案及格式的處理方法

3.1多個資料檔案，匯入同一張表

      通常對於邏輯比較複雜的系統可能存在這種情況，因為匯出的資料來源於多個系統， 因此可能提供給DBA的也是多個資料檔案。這種情況並不一定需要執行多次載入，只需要在控制檔案中做適當配置即可。不過有一點非常重要，提供的資料檔案中的資料存放格式必須完全相同。

 

建立演示表tb_manager表

 

--建立tb_manager表  

SCOTT@seiang11g>create table tb_manager(mgrno number,mname varchar2(30),job varchar2(300),remark varchar2(4000));

Table created.

 

有多個資料檔案，分別如下：

 

--資料檔案1  
[oracle@wjq SQL*Loader]$ vim wjq_test8_1.dat
10,SMITH,SALES MANAGER
11,ALLEN.W,TECH MANAGER
16,BLAKE,HR MANAGER 
18,WJQ,TEACHER MASTER
  
--資料檔案2  
[oracle@wjq SQL*Loader]$ vim wjq_test8_2.dat  
12,WARD,SERVICE MANAGER
13,TURNER,SELLS DIRECTOR
15,JAMES,HR DIRECTOR
  
--資料檔案3  
[oracle@wjq SQL*Loader]$ vim wjq_test8_3.dat  
17,MILLER,PRESIDENT

 

建立控制檔案，制定多個INFILE引數即可，控制檔案如下：

 

--控制檔案  
[oracle@wjq SQL*Loader]$ vim wjq_test8.ctl 
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test8_1.dat' 
INFILE '/u01/app/oracle/SQL*Loader/wjq_test8_2.dat' 
INFILE '/u01/app/oracle/SQL*Loader/wjq_test8_3.dat' 
TRUNCATE INTO TABLE tb_manager
FIELDS TERMINATED BY ","
(
 MGRNO,MNAME,JOB
)

 

執行sqlldr命令，並檢視結果

 

--執行sqlldr命令  
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test8.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:45:43 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4
Commit point reached - logical record count 7
Commit point reached - logical record count 8

  

--檢視結果  

SCOTT@seiang11g>select * from tb_manager;

     MGRNO MNAME                JOB                            REMARK
---------- -------------------- ------------------------------ ------------------------------
        10 SMITH                SALES MANAGER
        11 ALLEN.W              TECH MANAGER
        16 BLAKE                HR MANAGER
        18 WJQ                  TEACHER MASTER
        12 WARD                 SERVICE MANAGER
        13 TURNER               SELLS DIRECTOR
        15 JAMES                HR DIRECTOR
        17 MILLER               PRESIDENT

 

 

3.2同一個資料檔案，匯入不同表

控制檔案提供了多種邏輯判斷方式，只要能把邏輯清晰地描述出來，SQL*Loader就能 按照指定的邏輯執行載入。

 

資料檔案如下：

 

--資料檔案  

[oracle@wjq SQL*Loader]$ cat wjq_test9.dat 
BON  SMITH CLEAK       3904  
BON  ALLEN SALER,M     2891  
BON  WARD  SALER,"S"   3128  
BON  KING  PRESIDENT   2523  
MGR  10 SMITH    SALES MANAGER  
MGR  11 ALLEN.W  TECH MANAGER  
MGR  16 BLAKE    HR MANAGER  
TMP  SMITH 7369 CLERK    1020 20  
TMP  ALLEN 7499 SALESMAN 1930 30  
TMP  WARD  7521 SALESMAN 1580 30  
TMP  JONES 7566 MANAGER  3195 20

 

需求是將MGR開頭的記錄匯入到tb_manager表，以BON開頭的記錄匯入到tb_loader表，其他記錄存放到廢棄檔案中，建立控制檔案如下:

 

--控制檔案  

[oracle@wjq SQL*Loader]$ cat wjq_test9.ctl 
LOAD DATA  
INFILE '/u01/app/oracle/SQL*Loader/wjq_test9.dat'
DISCARDFILE '/u01/app/oracle/SQL*Loader/wjq_test9.dsc' 
TRUNCATE
INTO TABLE tb_loader
WHEN TAB='BON'  
(  
 TAB FILLER POSITION(1:3),  
 ENAME POSITION(6:10),  
 JOB POSITION(*+1:20),  
 SAL POSITION(*+3:27)  
)  
INTO TABLE tb_manager
WHEN TAB='MGR'  
(  
 TAB FILLER POSITION(1:3),  
 MGRNO POSITION(6:7),  
 MNAME POSITION(9:15),  
 JOB POSITION(*+2:30)  
) 

 

雖然這個控制檔案看起來比之前的都要複雜，但只有一個新語法，即關鍵字，我們這裡通過WHEN來實現判斷，很容易理解。同時，指定了DISCARDFILE引數，以生成不滿足載入條件的廢棄檔案，如果你有心，不妨等執行完sqlldr命令後査看wjq_test9.dsc檔案和wjq_test9.log檔案。
    另外注意，控制檔案中WHEN邏輯判斷不支援OR關鍵字，因此如果你的判斷條件有多個，則只能通過AND連線，而不能直接使用OR。

執行sqlldr命令，並檢視結果

 

--執行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test9.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:55:48 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 11

  

--檢視結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB                                   SAL       COMM
---------- ------------------------------ ---------- ----------
SMITH      CLEAK                                3904
ALLEN      SALER,M                              2891
WARD       SALER,"S"                            3128
KING       PRESIDENT                            2523

SCOTT@seiang11g>select * from tb_manager;

     MGRNO MNAME                JOB                            REMARK
---------- -------------------- ------------------------------ ------------------------------
        10 SMITH                SALES MANAGER
        11 ALLEN.W              TECH MANAGER
        16 BLAKE                HR MANAGER

 

這裡貼一下log日誌和廢棄日誌

 

--log日誌  

[oracle@wjq SQL*Loader]$ cat wjq_test9.log 

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:55:48 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   /u01/app/oracle/SQL*Loader/wjq_test9.ctl
Data File:      /u01/app/oracle/SQL*Loader/wjq_test9.dat
  Bad File:     /u01/app/oracle/SQL*Loader/wjq_test9.bad
  Discard File: /u01/app/oracle/SQL*Loader/wjq_test9.dsc 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TB_LOADER, loaded when TAB = 0X424f4e(character 'BON')
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TAB                                   1:3     3           CHARACTER            
  (FILLER FIELD)
ENAME                                6:10     5           CHARACTER            
JOB                             NEXT+1:20    19           CHARACTER            
SAL                             NEXT+3:27    24           CHARACTER            

Table TB_MANAGER, loaded when TAB = 0X4d4752(character 'MGR')
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TAB                                   1:3     3           CHARACTER            
  (FILLER FIELD)
MGRNO                                 6:7     2           CHARACTER            
MNAME                                9:15     7           CHARACTER            
JOB                             NEXT+2:30    28           CHARACTER            

Record 8: Discarded - failed all WHEN clauses.
Record 9: Discarded - failed all WHEN clauses.
Record 10: Discarded - failed all WHEN clauses.
Record 11: Discarded - failed all WHEN clauses.

Table TB_LOADER:
  4 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  7 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Table TB_MANAGER:
  3 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  8 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                   7168 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            11
Total logical records rejected:         0
Total logical records discarded:        4

Run began on Tue Oct 31 15:55:48 2017
Run ended on Tue Oct 31 15:55:48 2017

Elapsed time was:     00:00:00.08
CPU time was:         00:00:00.02

 

--廢棄日誌  

[oracle@wjq SQL*Loader]$ cat wjq_test9.dsc 
TMP  SMITH 7369 CLERK    1020 20  
TMP  ALLEN 7499 SALESMAN 1930 30  
TMP  WARD  7521 SALESMAN 1580 30  
TMP  JONES 7566 MANAGER  3195 20

 

 

3.3資料檔案前N行不想匯入

假如某天你接到一項資料載入需求，使用者提供了一份100萬行的資料檔案，告訴你只導後50萬行，恭喜，你接到了一個正常的需求！
實現的方式較多，比如修改資料檔案，只保留後50萬行（Windows下藉助EditPlus 這類文字工具可以輕鬆實現，Linux/UNIX下通過TAIL等命令也可以輕易實現），如果你人很懶，不想修改檔案，那正合sqlldr胃口，人家早早地就提供好了SKIP引數專用於滿足此類需求。

資料檔案如下：

 

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test10.dat
#This is data of emp
ENAME        MGR JOB             SAL
---------- ----- --------- ---------
SMITH       7902 CLERK         1020
  LEN       7698 SALESMAN      1930
▽ARD        7698 SALESMAN      1580
JONES       7839 MANAGER       3195
MARTIN      7698 SALESMAN      1580
BLAKE       7839 MANAGER       3180
CLARK       7839 MANAGER       2172
SCOTT       7566 ANALYST       3220
KING             PRESIDENT     4722
TURNER      7698 SALESMAN      1830
ADAMS       7788 CLERK         1320
JAMES       7698 CLERK         1280
FORD        7566 ANALYST       3220
MILLER      7782 CLERK         1022

 

我們只對該資料檔案只從第4行開始匯入，即前3行不進行匯入，建立控制檔案如下：

 

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test10.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test10.dat'  
TRUNCATE INTO TABLE tb_loader
(  
 ENAME position(1:6), 
 XCOL FILLER position(13:16),
 JOB position(18:26),
 SAL position(32:35)
)

 

執行sqlldr命令，並檢視結果

 

--執行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test10.ctl skip=3

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 16:12:11 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 14

  

--檢視結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB                                   SAL       COMM
---------- ------------------------------ ---------- ----------
SMITH      CLERK                                1020
ALLEN      SALESMAN                             1930
WARD       SALESMAN                             1580
JONES      MANAGER                              3195
MARTIN     SALESMAN                             1580
BLAKE      MANAGER                              3180
CLARK      MANAGER                              2172
SCOTT      ANALYST                              3220
KING       PRESIDENT                            4722
TURNER     SALESMAN                             1830
ADAMS      CLERK                                1320
JAMES      CLERK                                1280
FORD       ANALYST                              3220
MILLER     CLERK                                1022

 

如果使用者要求較高，明確指定只載入第XX到第XX行的記錄，sqlldr還有一個引數叫LOAD,配置LOAD引數即可輕鬆實現。
這裡仍使用上述資料檔案，需求改為只匯入第4到9行的記錄，我們連控制檔案都不需要修改，只需要在執行sqlldr時再加上LOAD引數即可：
執行sqlldr命令(即skip=3跳過前3行，load=6，載入接下來的6行記錄)，並檢視結果

 

--執行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test10.ctl skip=3 load=6

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 16:15:13 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 6

  

--檢視結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB                                   SAL       COMM
---------- ------------------------------ ---------- ----------
SMITH      CLERK                                1020
ALLEN      SALESMAN                             1930
WARD       SALESMAN                             1580
JONES      MANAGER                              3195
MARTIN     SALESMAN                             1580
BLAKE      MANAGER                              3180

 

 

3.4載入的資料中有換行符

由於標準換行符也是sqlldr識別資料行結束的標誌符，因此要將含換行符的資料載入到表中稍複雜一點點，而且需要根據實際情況來處理，不同情況的處理方式也不一樣， 但基本思路是相同的，就是要同sqlldr指明什麼時候才需要進行換行操作。

3.4.1手工指定的換行符

在手工指定換行符的情況下，資料檔案中的換行符並不是標準的換行標誌，而是使用者自定義的一個標識字元（或多個字元組成），這種情況的處理比較簡單，如資料檔案如下：

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test11_1.dat
10,SMITH,SALES MANAGER,This is SMITH.\nHe is a Sales Manager.
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.\nHe is a Tech Manager.
16,BLAKE,HR MANAGER,This is BLAKE.\nHe is a Hr Manager.

 

我們可以通過控制檔案，在資料載入前處理remark列的資料，將使用者指定的字元替換為chr(10),即標準換行符，建立控制檔案如下：

 

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test11_1.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_1.dat'  
TRUNCATE INTO TABLE tb_manager
FIELDS TERMINATED BY ","
(
 MGRNO,
 MNAME,
 JOB,
 REMARK "REPLACE(:remark,'\\n',chr(10))"
)

 

這裡需要注意的是，替換時必須指定"\\n"而不只是"\n"，因為"\n"會被SQLLDR 識別成換行符並轉換成換行標誌，這樣可能導致資料載入出錯。而是預設轉義符，指定該轉義符後sqlldr就會將"\n"識別成普通字元了。

執行sqlldr命令，並檢視結果

 

--執行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test11_1.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 16:21:04 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3

  

--檢視結果  

SCOTT@seiang11g>select * from tb_manager;

     MGRNO MNAME                JOB                            REMARK
---------- -------------------- ------------------------------ ------------------------------
        10 SMITH                SALES MANAGER                  This is SMITH.
                                                               He is a Sales Manager.

        11 ALLEN.W              TECH MANAGER                   This is ALLEN.W.
                                                               He is a Tech Manager.

        16 BLAKE                HR MANAGER                     This is BLAKE.
                                                               He is a Hr Manager.

 

 

3.4.2指定FIX屬性處理換行符

資料檔案如下：

 

--資料檔案  

[oracle@wjq SQL*Loader]$ cat wjq_test11_2.dat 
10 SMITH   SALES MANAGER THIS IS SMITH.  
HE IS A SALES MANAGER.
11 ALLEN.W TECH MANAGER  THIS IS ALLEN.W 
HE IS A TECH MANAGER. 
16 BLAKE   HR MANAGER    THIS IS BLAKE.  
HE IS A HR MANAGE.   

（特別注意：因為使用的FIX固定長度，所以一定要注意每一行資料的長度，不夠的使用空格來代替，否則在載入的使用會報錯，報錯資訊如下所示：

SQL*Loader-501: Unable to read file (wjq_test11_2.dat)

SQL*Loader-566: partial record found at end of datafile

SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

報錯的原因並不是說讀不到要載入的資料檔案，相反，雖然出現了報錯的資訊，也有可能部分資料已經匯入進去了，報錯因為你的存在資料並不是你所FIX的長度，所以出現報錯，故在使用FIX的時候一定要注意這一點）

 

建立控制檔案如下：

 

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test11_2.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_2.dat' "FIX 65"  
TRUNCATE INTO TABLE tb_manager
(
 MGRNO POSITION(1:2),
 MNAME POSITION(*+1:10),
 JOB POSITION(*+1:24),
 REMARK POSITION(*+1:63)
)

 

FIX是INFILE關鍵字的一個屬性，INFILE不僅有FIX屬性，還有VAR和STR等屬性

執行sqlldr命令，並檢視結果

 

--執行sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test11_2.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 10:37:45 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3

  

--檢視結果  

SCOTT@seiang11g>select * from tb_manager;

     MGRNO MNAME                JOB                            REMARK
---------- -------------------- ------------------------------ ----------------------------------------
        10 SMITH                SALES MANAGER                  THIS IS SMITH.
                                                               HE IS A SALES MANAGER.

        11 ALLEN.W              TECH MANAGER                   THIS IS ALLEN.W.
                                                               HE IS A TECH MANAGER.

        16 BLAKE                HR MANAGER                     THIS IS BLAKE.
                                                               HE IS A HR MANAGER.

 

這種方式其實就是在載入資料檔案之前，先通過FIX屬性指定每行的長度（這裡每行65個字元，包括換行符在內,該例子上不夠字元的用空格來代替了，注意上面資料檔案中的結尾的空格），到了指定長度就換行，不管中間有沒有換行符，因此僅能用於定長字串的資料檔案，因為只有字串定長，你才知道應該在INFILE處指定什麼值。

 

針對上述的資料檔案也可以通過使用CONCATENATE屬性處理換行符來處理換行符，控制檔案內容如下所示：
[oracle@wjq SQL*Loader]$ vim wjq_test11_2.ctl 
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_2.dat'  
CONCATENATE 2 INTO TABLE tb_manager         
(
 MGRNO POSITION(1:2),
 MNAME POSITION(*+1:10),
 JOB POSITION(*+1:24),
 REMARK POSITION(*+1:63) "REPLACE(upper(:REMARK),'HE',chr(10)||'HE')"
)

 

 

3.4.3指定VAR屬性處理換行符（行頭部標識換行）

前面提到INFILE關鍵字還支援VAR屬性，語法格式為INFILE filename "var n", n的值不能超過40,否則會報錯，如果不指定n則預設值為5。
本小節就演示通過這種方式處理換行符。總的來說，這確實是相當有才的一種方式， 首先通過VAR屬性在每行開頭指定一個固定長度的字串，該字串指明該行的長度，通過這種方式支援變長字串。

資料檔案如下：

 

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test11_3.dat
06110,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.
06311,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.
05516,BLAKE,HR MANAGER,This is BLAKE.
He is a Hr Manager.

資料檔案中每行開頭的061，063，055分別表示該行取61，63，55個字元

 

建立控制檔案如下：

 

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test11_3.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_3.dat' "var 3"  
TRUNCATE INTO TABLE tb_manager
FIELDS TERMINATED BY ","
(MGRNO,MNAME,JOB,REMARK)

 

執行sqlldr命令，並檢視結果

 

--執行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test11_3.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 09:51:12 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3

  

--檢視結果  

SCOTT@seiang11g>select * from tb_manager;

     MGRNO MNAME                JOB                            REMARK
---------- -------------------- ------------------------------ ----------------------------------------
        10 SMITH                SALES MANAGER                  This is SMITH.
                                                               He is a Sales Manager.

        11 ALLEN.W              TECH MANAGER                   This is ALLEN.W.
                                                               He is a Tech Manager.

        16 BLAKE                HR MANAGER                     This is BLAKE.
                                                               He is a Hr Manager.

①這種方式可以視為第2種方式的變種，該方法不在INFILE處指定行長度，而改為在每行的行首位置標註該行長度，以支援變長型別的資料格式。

②綜合來看，這兩種方式實用價值都比較低，先不說使用者是否真有如此耐心來生成這樣格式的資料檔案，就算使用者按照這種格式提供，要知道windows、Linux/UNIX下因作業系統自身原因,對換行符識別也不同，在Windows環境下換行標誌由"回車chr(13)+換行chr(10)" 兩個位元組組成，而Linux/UNIX環境則是"換行chr(10)"一個位元組，也就是說指定了長度，作業系統變了以後，長度有可能也得跟著變，這就造成使用者提供的資料檔案通用性較差。

③有沒有一種更簡單的方式呢？事實上確實沒有太簡單的方式，在前面的內容中就提到， 你需要為sqlldr指明什麼時候應該換行，因此對資料檔案進行預處理是必然的，不過相對來講，下面將要介紹的方式更易於操作，也更可行一些。

 

 

3.4.4指定STR屬性處理換行符（行尾部標識換行）

這種方式也需要先對資料檔案做處理，在記錄換行處打上一個標記，比如"丨"（當然可以定義為其他字元，但注意不要與要匯入的資料有衝突），這樣sqlldr見到該字元就知道換行的時候到了。
    由於單個字元出現在匯入資料中的機率較高，因此建議換行標誌儘可能由多個字元組成，通常習慣於定義“字元+換行符”作為新的換行標記，這裡我們也採用這種方式。

資料檔案和控制檔案如下：

 

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test11_4.dat
10,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.|
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.|
16,BLAKE,HR MANAGER,This is BLAKE.
He is a Hr Manager.|

  

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test11_4.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_4.dat' "str '|\n'"  
TRUNCATE INTO TABLE tb_manager
FIELDS TERMINATED BY ","
(MGRNO,MNAME,JOB,REMARK)

 

執行sqlldr命令，並檢視結果

 

--執行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test11_4.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 09:45:28 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3

  

--檢視結果  

SCOTT@seiang11g>select * from tb_manager;

     MGRNO MNAME                JOB                            REMARK
---------- -------------------- ------------------------------ ----------------------------------------
        10 SMITH                SALES MANAGER                  This is SMITH.
                                                               He is a Sales Manager.

        11 ALLEN.W              TECH MANAGER                   This is ALLEN.W.
                                                               He is a Tech Manager.

        16 BLAKE                HR MANAGER                     This is BLAKE.
                                                               He is a Hr Manager.

 

STR屬性中支援兩種字元指定方式：
'char_string':普通字元，即標準的可見字元，不過也有些不可見字元可以通過下列反斜槓標識的方式在字串模式中指定：
\n:表示換行。
\t:表示行製表符(tab)。
\f:表示換頁。
\v:表示列製表符。
\r:表示回車。
    說到這裡，又不得不再次提及Windows和Linux/UNIX對換行符識別的差異，Linux/UNIX下指定"\n"即可，Windows下需要指定"\r\n"才表示一個完整的換行符。
    X'hex_string':二進位制字元。對於一些不可見字元，如像回車換行這類字元，可以將其轉換成十六進位制，然後再通過str X'hex_str'方式指定。

比如上述控制檔案中的功能如果用二進位制字元表示，形式如下:

INFILE ldr_case11_4.dat "str X'7C0A'"  

 

要査看指定字元的十六進位制編碼，可以通過UTL_RAW.CAST_TO_RAW生成，例如:

SCOTT@seiang11g>select utl_raw.cast_to_raw('|'||chr(10)) from dual;

UTL_RAW.CAST_TO_RAW('|'||CHR(10))
--------------------------------------------------------------------------------
7C0A

這種方式相當於自定義一個換行標誌，標準換行符不再擁有特殊的意義，只是作為要載入資料的一部分。較第一種方式而言最大的優勢是，資料檔案相對更容易處理，只需要在生成資料檔案時，最後一列附加一個指定字元即可，對於稍有SQL基礎的人來說，這都是小case。

---

---

About Me

............................................................................................................................................. ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ群號：230161599（滿）、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友（646634621），註明新增緣由 ● 於 2017-11-01 09:00 ~ 2017-11-30 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................................. ● 小麥苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。    小麥苗的微信公眾號      小麥苗的DBA寶典QQ群2     《DBA筆試面寶典》讀者群       小麥苗的微店 .............................................................................................................................................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2146660/，如需轉載，請註明出處，否則將追究法律責任。