我有一个严重的sqlldr问题困扰着我.我的控制文件看起来像这样：
load data infile 'txgen.dat' into table TRANSACTION_NEW fields terminated by "," optionally enclosed by '"' TRAILING NULLCOLS ( A,B,C,D,ID "ID_SEQ.NEXTVAL" )
数据是这样的：
a,b,c,a,d a,d
如果我没有把TRAILING NULLCOLS放在里面,我会得到“逻辑记录结束之前未找到的列”错误.但是尽管有一些列为空,但是逗号都是这样的,所以我看不到sqlldr的错误解释输入文件的原因,而不是从数据库序列生成ID.
这个语法之前没有空列 – 为什么一个空列会导致sqlldr没有到达生成的列？
我有它的工作,我只是想明白为什么！
您已经在控制文件中定义了5个字段.您的字段由逗号终止,因此,除非指定了TRAILING NULLCOLS,否则即使您通过sql字符串加载具有序列值的ID字段,则在5个字段的每个记录中都需要5个逗号.
RE：评论由OP
这不是我经历一个短暂的考验.使用以下控制文件：
load data infile * into table T_new fields terminated by "," optionally enclosed by '"' ( A,ID "ID_SEQ.NEXTVAL" ) BEGINDATA 1,1,2,3,4,
Table T_NEW,loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
A FIRST *,O(") CHARACTER
B NEXT *,O(") CHARACTER
C NEXT *,O(") CHARACTER
D NEXT *,O(") CHARACTER
ID NEXT *,O(") CHARACTER
sql string for column : "ID_SEQ.NEXTVAL"
Record 1: Rejected - Error on table T_NEW,column ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table T_NEW,column ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table T_NEW,column ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 5: Discarded - all columns null.
Table T_NEW:
1 Row successfully loaded.
3 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were Failed.
1 Row not loaded because all fields were null.
请注意,唯一正确加载的行有5个逗号.即使是第三行,除ID之外,所有数据值都不存在,数据不会加载.除非我错过了一些…
我使用的是10gR2.
