需要 Oracle sqlldr TRAILING NULLCOLS，但为什么呢？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3917337/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle sqlldr TRAILING NULLCOLS required, but why?
提问by orbfish
I have an abstruse sqlldr problem that's bothering me. My control file looks something like this:
我有一个深奥的 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"
)
Data is something like this:
数据是这样的：
a,b,c,
a,b,,d
a,b,,
a,b,c,d
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.
如果我不输入 TRAILING NULLCOLS，我会收到“在逻辑记录结束前找不到列”错误。但是虽然有些列是空的，逗号都在那里，所以我没有看到 sqlldr 误解输入文件的原因，并且没有走到从数据库序列生成 ID 的最后。
This syntax has worked before with no null columns - why does a null column cause sqlldr to not reach the generated column?
此语法以前在没有空列的情况下工作过 - 为什么空列会导致 sqlldr 无法到达生成的列？
I've got it working, I just want to understand WHY!?!
我已经开始工作了，我只是想了解为什么！？！
采纳答案by DCookie
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.
您已在控制文件中定义了 5 个字段。您的字段以逗号结尾，因此您需要在 5 个字段的每条记录中使用 5 个逗号，除非指定了 TRAILING NULLCOLS，即使您通过 SQL 字符串加载具有序列值的 ID 字段。
RE: Comment by OP
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,,
,,,,,
Produced the following output:
产生了以下输出：
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.
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...
请注意，唯一正确加载的行有 5 个逗号。即使是第 3 行，除了 ID 之外的所有数据值都存在，数据也不会加载。除非我遗漏了什么......
I'm using 10gR2.
我正在使用 10gR2。
回答by Chris
The problem here is that you have defined ID as a field in your data file when what you want is to just use an expression without any data from the data file. You can fix this by defining ID as an expression (or a sequence in this case)
这里的问题是您已经将 ID 定义为数据文件中的一个字段，而您想要的只是使用一个没有来自数据文件的任何数据的表达式。您可以通过将 ID 定义为表达式（或本例中的序列）来解决此问题
ID EXPRESSION "ID_SEQ.nextval"
or
或者
ID SEQUENCE(count)
See: http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_field_list.htm#i1008234for all options
有关所有选项，请参阅：http: //docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_field_list.htm#i1008234
回答by juzz4fun
Last column in your input file must have some data in it (be it space or char, but not null). I guess, 1st record contains null after last ',' which sqlldr won't recognize unless specifically asked to recognize nulls using TRAILING NULLCOLS option. Alternatively, if you don't want to use TRAILING NULLCOLS, you will have to take care of those NULLs before you pass the file to sqlldr. Hope this helps
输入文件中的最后一列必须包含一些数据（可以是空格或字符，但不能为空）。我猜，第一条记录在最后一个 ',' 之后包含空值，除非特别要求使用 TRAILING NULLCOLS 选项识别空值，否则 sqlldr 将无法识别。或者，如果您不想使用 TRAILING NULLCOLS，则必须在将文件传递给 sqlldr 之前处理这些 NULL。希望这可以帮助
回答by WizzArt
I had similar issue when I had plenty of extra records in csv file with empty values. If I open csv file in notepad then empty lines looks like this: ,,,, ,,,, ,,,, ,,,,
当我在 csv 文件中有大量带有空值的额外记录时，我遇到了类似的问题。如果我在记事本中打开 csv 文件，则空行如下所示： ,,,, ,,,, ,,,, ,,,,
You can not see those if open in Excel. Please check in Notepad and delete those records
如果在 Excel 中打开，您将看不到那些。请检查记事本并删除这些记录
回答by ram
Try giving 5 ',' in every line, similar to line number 4.
尝试在每一行中给出 5 ','，类似于第 4 行。
