oracle sqlload 怎么会插入末尾回车符号,SQLLDR装载换行符的小问题

Tom在EXPERT ONE ON ONE ORACLE中的数据装载一章详细介绍了SQLLDR的使用方法和技巧，其中也介绍了几种装载换行符的方法。

但是今天在ITPUB上看到一个帖子，是关于Tom介绍的那个例子的，仔细看了一下，Tom给出的例子和说明还存在一点小的问题。

首先来构建SQLLDR的例子，为了突出问题，将例子尽量简化：

SQL> CREATE TABLE TT (ID NUMBER, COMMENTS VARCHAR2(60));

表已创建。

控制文件的内容如下：

LOAD DATA

INFILE *

INTO TABLE TT

REPLACE

FIELDS TERMINATED BY ','

(

ID,

COMMENTS "REPLACE(:COMMENTS,'\n',CHR(10))"

)

BEGINDATA

10,This is the Sales\nOffice in Virginia

20,This is the Accounting\nOffice in Virginia

30,This is the Consulting\nOffice in Virginia

40,This is the Finance\nOffice in Virginia

使用SQLLDR导入数据，发现\n并没有被替换成换行：

SQL> HOST SQLLDR YANGTK/YANGTK CONTROL=E:\TEST.CTL

SQL> SELECT * FROM TT;

ID COMMENTS

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

10 This is the Sales\nOffice in Virginia

20 This is the Accounting\nOffice in Virginia

30 This is the Consulting\nOffice in Virginia

40 This is the Finance\nOffice in Virginia

但是，如果将\n都替换为/n，则导入结果正确：

LOAD DATA

INFILE *

INTO TABLE TT

REPLACE

FIELDS TERMINATED BY ','

(

ID,

COMMENTS "REPLACE(:COMMENTS,'/n',CHR(10))"

)

BEGINDATA

10,This is the Sales/nOffice in Virginia

20,This is the Accounting/nOffice in Virginia

30,This is the Consulting/nOffice in Virginia

40,This is the Finance/nOffice in Virginia

SQL> HOST SQLLDR YANGTK/YANGTK CONTROL=E:\TEST.CTL

SQL> SELECT * FROM TT;

ID COMMENTS

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

10 This is the Sales

Office in Virginia

20 This is the Accounting

Office in Virginia

30 This is the Consulting

Office in Virginia

40 This is the Finance

Office in Virginia

而且测试发现，如果使用\n的话，只需要将REPLACE函数中的\n修改为\\n即可：

LOAD DATA

INFILE *

INTO TABLE TT

REPLACE

FIELDS TERMINATED BY ','

(

ID,

COMMENTS "REPLACE(:COMMENTS,'\\n',CHR(10))"

)

BEGINDATA

10,This is the Sales\nOffice in Virginia

20,This is the Accounting\nOffice in Virginia

30,This is the Consulting\nOffice in Virginia

40,This is the Finance\nOffice in Virginia

SQL> HOST SQLLDR YANGTK/YANGTK CONTROL=E:\TEST.CTL

SQL> SELECT * FROM TT;

ID COMMENTS

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

10 This is the Sales

Office in Virginia

20 This is the Accounting

Office in Virginia

30 This is the Consulting

Office in Virginia

40 This is the Finance

Office in Virginia

这是导入结果也是正确的，不过这就和Tom所说的有所偏差。根据Tom的观点，只有在UNIX环境下才需要使用\\n，而windows环境下\n是可以正常工作的。

但是通过测试却发现，无论是windows环境还是UNIX环境，都必须使用\\n才能得到正确的结果。Tom认为在UNIX环境下\n是换行标识，因此UNIX环境需要额外的\来标识。但是我怀疑问题不是操作系统环境造成的，而是C语言的语法造成的。

SQLLDR是Oracle最迅速的装载工具，这个工具是用C语言写的恐怕不是什么值得怀疑的事情。如果这个假设成立的话，那么问题产生的原因就很明显了。在控制文件中，COMMENTS列的格式控制部分是放在双引号中的："REPLACE(:COMMENTS,'\n',CHR(10))"。也就是说，这部分SQLLDR是当作字符串读入的。根据C语言的性质，字符串中的’\’字符是转义符，也就是说这个’\n’被C语言解释成了换行符，因此REPLACE函数实际上变成了换行符替换为换行符的操作，这也是REPLACE操作没有生效的原因。在语言中为了处理转移符’\’，必须使用’\\’。这就是为什么\\n可以生效。

最后为了验证我的观点，对上面的控制文件进行简单的修改：

LOAD DATA

INFILE *

INTO TABLE TT

REPLACE

FIELDS TERMINATED BY ','

(

ID,

COMMENTS "REPLACE(REPLACE(:COMMENTS, ' ', CHR(10)), '\n', '!')"

)

BEGINDATA

10,This is the Sales\nOffice in Virginia

20,This is the Accounting\nOffice in Virginia

30,This is the Consulting\nOffice in Virginia

40,This is the Finance\nOffice in Virginia

SQL> HOST SQLLDR YANGTK/YANGTK CONTROL=E:\TEST.CTL

SQL> SELECT * FROM TT;

ID COMMENTS

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

10 This!is!the!Sales\nOffice!in!Virginia

20 This!is!the!Accounting\nOffice!in!Virginia

30 This!is!the!Consulting\nOffice!in!Virginia

40 This!is!the!Finance\nOffice!in!Virginia

果然不出所料。不过以Tom的功力应该是不会犯如此低级的错误的，而且我相信Tom也不大可能将没有测试过的例子直接写到书中。在Oracle的低版本测试中和我的测试有所出入亦未可知。我的测试版本是Oracle 10.2.0.1。