Oracle sqlldr 需要 TRAILING NULLCOLS，但为什么呢？

发布于 2024-09-27 19:22:29 字数 501 浏览 4 评论 0原文

我有一个深奥的 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

如果我不放入尾随 NULLCOLS，我会收到“在逻辑记录结束之前找不到列”错误。但是，尽管有些列为空，但逗号都在那里，所以我不认为 sqlldr 会误解输入文件，并且不会到达从数据库序列生成 ID 的末尾。

此语法以前在没有空列的情况下有效 - 为什么空列会导致 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"
)

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.

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!?!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

回眸一遍 2024-10-04 19:22:29

您已在控制文件中定义了 5 个字段。您的字段以逗号终止，因此除非指定了 TRAILING NULLCOLS，否则即使您通过 SQL 字符串加载带有序列值的 ID 字段，您也需要在 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。

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: Comment by 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...

I'm using 10gR2.

回复收藏 0 原文

初雪 2024-10-04 19:22:29

这里的问题是，当您想要只使用表达式而不使用数据文件中的任何数据时，您已将 ID 定义为数据文件中的字段。您可以通过将 ID 定义为表达式（或在本例中为序列）来解决此问题

ID EXPRESSION "ID_SEQ.nextval"

，或者

ID SEQUENCE(count)

参见：http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_field_list.htm#i1008234 了解所有选项

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 EXPRESSION "ID_SEQ.nextval"

ID SEQUENCE(count)

See: http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_field_list.htm#i1008234 for all options

回复收藏 0 原文

韬韬不绝 2024-10-04 19:22:29

输入文件中的最后一列必须包含一些数据（可以是空格或字符，但不能为空）。我猜想，第一条记录在最后一个“，”之后包含空值，除非特别要求使用 TRAILING NULLCOLS 选项识别空值，否则 sqlldr 将无法识别该空值。
或者，如果您不想使用 TRAILING NULLCOLS，则必须在将文件传递给 sqlldr 之前处理这些 NULL。
希望这有帮助

回复收藏 0 原文