需要Oracle sqlldr TRAILING NULLCOLS，但是为什么呢? [英] Oracle sqlldr TRAILING NULLCOLS required, but why?

问题描述

我遇到一个麻烦的sqlldr问题.我的控制文件如下所示:

I have an abstruse sqlldr problem that's bothering me. My control file looks something like this:

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的末尾.

If I don't put the TRAILING NULLCOLS in, I get the "column not found before end of logical record" error. But although some of the columns are null, the commas are all there, so I don't see a reason for sqlldr to misinterpret the input file, and not get to the end where it generates the ID from the database sequence.

此语法以前没有空列就可以使用-为什么空列会导致sqlldr无法到达生成的列?

This syntax has worked before with no null columns - why does a null column cause sqlldr to not reach the generated column?

我已经开始工作了，我只想了解为什么！?！

I've got it working, I just want to understand WHY!?!

推荐答案

您已在控制文件中定义了5个字段.您的字段以逗号结尾，因此，即使您通过SQL字符串使用序列值加载ID字段，除非指定了TRAILING NULLCOLS，否则对于5个字段，每条记录都需要5个逗号.

You have defined 5 fields in your control file. Your fields are terminated by a comma, so you need 5 commas in each record for the 5 fields unless TRAILING NULLCOLS is specified, even though you are loading the ID field with a sequence value via the SQL String.

RE:OP的评论

这不是我进行简短测试的经验.带有以下控制文件:

That's not my experience with a brief test. With the following control file:

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以外，所有数据值都存在，数据也不会加载.除非我想念什么...

Note that the only row that loaded correctly had 5 commas. Even the 3rd row, with all data values present except ID, the data does not load. Unless I'm missing something...

我正在使用10gR2.

I'm using 10gR2.

这篇关于需要Oracle sqlldr TRAILING NULLCOLS，但是为什么呢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！