换行符是SQLLDR默认的行结束符号,在ORACLE816以后可以实现加载数据中带有换行行的数据。基本原则就是用一个非换行符的其它字符表表示数据中的换行符号。
测试:
会话1:修改测试表结构,建立如下控制文件
SQL> alter table dept_load add comments varchar2(4000);
Table altered.
SQL> desc dept_load;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
ENTIRE_LINE VARCHAR2(29)
LAST_UPDATED DATE
COMMENTS VARCHAR2(4000)
SQL>
[oracle@oraclelinux ~]$ cat dept_load14.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT_LOAD
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
COMMENTS "replace(:comments,'\\n',chr(10))"==\\n是用来替换换行符的
)
BEGINDATA
10,Sales,Virginia,This is the Sales\nOffice in Virginia
20,Accounting,Virginia,This is the Accounting\nOffice in Virginia
30,Consulting,Virginia,This is the Consulting\nOffice in Virginia
40,Finance,Virginia,This is the Finance\nOffice in Virginia
[oracle@oraclelinux ~]$
会话1:加载数据
oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load14.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 14:38:56 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 4
SQL> select deptno,dname,comments from dept_load;
DEPTNO DNAME COMMENTS
---------- -------------- --------------------
10 SALES This is the Sales
Office in Virginia
20 ACCOUNTING This is the Accounti
ng
Office in Virginia
30 CONSULTING This is the Consulti
ng
Office in Virginia
DEPTNO DNAME COMMENTS
---------- -------------- --------------------
40 FINANCE This is the Finance
Office in Virginia
测试结束
除了以下的方法外,可以用SQLLDR的FIX属性,前提是输入数据必须出现在定长记录中。
还有利用SQLLDR的VAR属性,使用这种格式时,每个记录必须以某个固定的字节数开始,这表示这个记录的总长度。
还有一种就是使用SQLLDR的STR属性,这可以用来指定一个新的行结束符号,这种情况下行结束处的换行符号对使用者不在特殊。此时可以把它当成普通符号。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-723519/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-723519/