我有一个令人困扰的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,b,,d
a,b,,
a,b,c,d
如果不放入TRAILING NULLCOLS，则会收到“逻辑记录结束前未找到列”的错误。但是，尽管某些列为空，但逗号全在那儿，因此我看不到sqlldr会误解输入文件的原因，并且不会从数据库序列生成ID的末尾。
此语法以前没有空列就可以使用-为什么空列会导致sqlldr无法到达生成的列？
我已经工作了，我只想了解为什么!？!
最佳答案
您在控制文件中定义了5个字段。您的字段以逗号结尾，因此，即使您通过SQL字符串使用序列值加载ID字段，除非指定了TRAILING NULLCOLS，否则对于5个字段，每条记录都需要5个逗号。
RE:OP发表的评论
这不是我进行简短测试的经验。带有以下控制文件:
load data
infile *
into table T_new
fields terminated by "," optionally enclosed by '"'
( A,
B,
C,
D,
ID "ID_SEQ.NEXTVAL"
)
BEGINDATA
1,1,,,
2,2,2,,
3,3,3,3,
4,4,4,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。