window換行符導致Linux下sqlldr載入圖片失敗

    前些時間在用SQLLDR載入圖片的時候，報圖片找不到，SQLLDR的資料檔案是在window主機上生成的，而且在window客戶機上可以正常載入，利用SSH工具上傳到linux伺服器上，只用VIM替換了圖片的路徑，按理說不應該找不到圖片的，如下：

[oracle@dbserver1 load]$ sqlldr scott/tiger control=sqlldr.ctl log=sqlldr.log

SQL*Loader: Release 11.2.0.2.0 - Production on Wed May 16 15:52:50 2012

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

' for field IMAGE table IMAGES data file '/oldsystemdata/gwf/0408.tif

SQL*Loader-553: file not found

SQL*Loader-509: System error: No such file or directory

' for field IMAGE table IMAGES data file '/oldsystemdata/gwf/1271.tif

SQL*Loader-553: file not found

SQL*Loader-509: System error: No such file or directory

' for field IMAGE table IMAGES data file '/oldsystemdata/gwf/0850.tif

SQL*Loader-553: file not found

SQL*Loader-509: System error: No such file or directory

    而且使用ls命令檢視圖片確實存在。

[oracle@dbserver1 load]$ ll /oldsystemdata/gwf/0746.tif

-rwxr-xr-x 1 root root 1126292 Nov 14 2002 /oldsystemdata/gwf/0746.tif

    因為之前遇到過很多window的檔案上傳到linux會多一位換行符導致檔案不可以直接呼叫的情況，所以首先想到的也是這個問題，仔細觀察SQLLDR日誌也會發現，日誌提示的圖片路徑的兩個單引號位置不對，而且第一行錯誤少了SQL*Loader-553和SQL*Loader-509部分，這就更有可能是由於window和linux的換行符不一致導致的，linux以$符號作為換行符，而window以^M$為換行符，用cat命令檢視SQLLDR的資料檔案：

[oracle@dbserver1 load]$ cat -A load.txt | grep head

1,05-0073-044,/oldsystemdata/gwf/0408.tif^M$

2,05-0073-044,/oldsystemdata/gwf/1271.tif^M$

3,05-0073-044,/oldsystemdata/gwf/0850.tif^M$

4,05-0073-044,/oldsystemdata/gwf/1176.tif^M$

5,05-0073-044,/oldsystemdata/gwf/0420.tif^M$

6,05-0073-044,/oldsystemdata/gwf/1305.tif^M$

7,05-0073-044,/oldsystemdata/gwf/0817.tif^M$

8,05-0073-044,/oldsystemdata/gwf/1464.tif^M$

9,05-0073-044,/oldsystemdata/gwf/1404.tif^M$

10,05-0074-057,/oldsystemdata/gwf/0005.tif^M$

    可見，的確是window換行符導致的問題，解決方法也很簡單，只要將^M替換掉就可以了。

[oracle@dbserver1 load]$ vim load.txt

700,02-0023-128,/oldsystemdata/gwf/0128.tif

701,02-0023-129,/oldsystemdata/gwf/0129.tif

702,02-0023-130,/oldsystemdata/gwf/0130.tif

703,02-0023-131,/oldsystemdata/gwf/0131.tif
704,02-0023-132,/oldsystemdata/gwf/0132.tif

:%s/^M//g

    按照以上命令回車後即可將^M符號替換掉，上面的^M符號不要複製貼上，按Ctrl+v+m即可，複製貼上有可能會提示找不到^M，替換掉^M符號後，再次執行SQLLDR，沒有報錯，資料正確載入。

[oracle@dbserver1 load]$ sqlldr scott/tiger control=sqlldr.ctl log=sqlldr.log

SQL*Loader: Release 11.2.0.2.0 - Production on Wed May 16 15:59:10 2012

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

Commit point reached - logical record count 64

Commit point reached - logical record count 128

Commit point reached - logical record count 192

Commit point reached - logical record count 256

Commit point reached - logical record count 320

Commit point reached - logical record count 384

Commit point reached - logical record count 448

Commit point reached - logical record count 512

Commit point reached - logical record count 576

Commit point reached - logical record count 640

Commit point reached - logical record count 704

    注：以上SQLLDR載入部分只為簡單實驗，並未最佳化，有興趣的朋友可以適當最佳化下。