linux sqlldr.sh,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.