Linux環境下sqlldr一個csv檔案

本來是練習三思的書裡一個sqlldr的小樣本，就是把excel檔案另存新檔csv後通過sqlldr載入到Oracle資料庫中。其目的本來是為了示範一下csv檔案的sqlldr以及csv檔案中的字串中存在逗號, 和雙引號”的處理方法，結果卻引出了一個讓我困惑了一陣子的問題，說大不大說小不小，反覆測試了一番，懷疑到了一個點上，最後一查果然是那個樣子，再測試就通過了，下面總結一下。
 
順便記錄一個：

三思說要建立scott這個經典的schema要運行$ORACLE_HOME/rdbms/admin/scott.sql這個指令碼的內容，而實際上我並沒找到這個指令碼，找到並啟動並執行是utlsampl.sql
 
excel是這樣子的：

SMITH	CLEAK	3904
ALLEN	SALER,M	2891
WARD	SALER,"S"	3128
KING	PRESIDENT	2523

 

另存新檔'ldr_case2.csv'後內容是：

SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523

一切看起都挺正常的，然後將csv上傳到Oracle所在的linux伺服器上，寫好control檔案：

load data
infile 'ldr_case2.csv'
truncate into table bonus
fields terminated by ',' optionally enclosed by '"'
(ename,job,sal)

執行載入後日誌顯示為失敗：

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

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

Record 1: Rejected - Error on table BONUS, column SAL.
ORA-01722: invalid number

Record 2: Rejected - Error on table BONUS, column SAL.
ORA-01722: invalid number

Record 3: Rejected - Error on table BONUS, column SAL.
ORA-01722: invalid number

Record 4: Rejected - Error on table BONUS, column SAL.
ORA-01722: invalid number

反覆測試後，終於發現我把檔案中的內容放到在linux下建立的檔案中，載入OK，但是看起內容一樣的csv怎麼改都不行，我就懷疑看起來一樣的東西是不是隱藏了什麼不為我察覺的差異。帶著這個疑問上網搜尋了一下，果然有人遇到相同的問題，隱藏的差異就是csv檔案行末藏了斷行符號符。在linux下查看對比：
 
[oracle@nathan-rhel5 ~]$ cat -v ldr_case2.csv
SMITH,CLEAK,3904^M
ALLEN,"SALER,M",2891^M
WARD,"SALER,""S""",3128^M
KING,PRESIDENT,2523^M
[oracle@nathan-rhel5 ~]$ cat -v ldr_case2.dat0
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523

原來作祟的就是檔案行末的^M啊！！！

把csv檔案轉一下格式：
[oracle@nathan-rhel5 ~]$ dos2unix ldr_case2.csv
dos2unix: converting file ldr_case2.csv to UNIX format ...
[oracle@nathan-rhel5 ~]$ cat -v ldr_case2.csv
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523

然後再重新載入一次資料成功了：
[oracle@nathan-rhel5 ~]$ vi ldr_case2.ctl

load data
infile 'ldr_case2.csv'
truncate into table bonus
fields terminated by ',' optionally enclosed by '"'
(ename,job,sal)
[oracle@nathan-rhel5 ~]$ sqlldr scott/tiger control=ldr_case2.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Feb 22 22:47:31 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 4
[oracle@nathan-rhel5 ~]$ vi ldr_case2.log

Control File:  ldr_case2.ctl
Data File:      ldr_case2.csv
  Bad File:    ldr_case2.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 BONUS, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

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

Table BONUS:
  4 Rows successfully loaded.