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加载部分只为简单实验，并未优化，有兴趣的朋友可以适当优化下。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26547977/viewspace-753001/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26547977/viewspace-753001/