SQL 加载器错误
我从 sql 加载器收到以下日志消息。此消息是否为“行未加载，因为所有 WHEN 子句均失败。”真的是错误吗？ 最后，我有一条日志消息，显示总记录读取为 74256。在 ctl 控制文件中，我在每行中看到记录类型的 38-39 字符 并基于它我将插入到不同的表中。
Table TEST1_AA:
42233 Rows successfully loaded.
0 Rows not loaded due to data errors.
32023 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table IIR_TEST2_BB:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
74256 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table IIR_TEST3_CC:
31 Rows successfully loaded.
0 Rows not loaded due to data errors.
74225 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Total logical records skipped: 0
Total logical records read: 74256
Total logical records rejected: 0
Total logical records discarded: 1
示例 CTL 文件
选项（ERRORS=9999999999、SILENT=(FEEDBACK)、ROWS=1000）
将数据加载
到表 test_HD 中，
附加
时 (038:039) = 'HD'
(
record_sequence_no POSITION(029:037) DECIMAL EXTERNAL
,line_type POSITION(040) :040) CHAR
,header_record_type POSITION(041:041) CHAR
,enquiry_telephone_number POSITION(042:052) CHAR
,total_amt_last_bill POSITION(053:066) DECIMAL EXTERNAL
,total_ payment_received POSITION(067:080) DECIMAL EXTERNAL
)
到表中_AS
追加
时间 (038 :039) = 'AS'
(
record_sequence_no POSITION(029:037) DECIMAL EXTERNAL
,line_type POSITION(040:040) CHAR
,girn POSITION(041:047) CHAR
,charge_desc POSITION(048:077) CHAR
}
I get the below log message from sql loader. Is this message "Rows not loaded because all WHEN clauses were failed." a really error?
At the end i have a log message saying total records read as 74256. In the ctl control file i see the 38-39 character for record type in each line
and based on it i will insert into different tables.
Table TEST1_AA:
42233 Rows successfully loaded.
0 Rows not loaded due to data errors.
32023 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table IIR_TEST2_BB:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
74256 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table IIR_TEST3_CC:
31 Rows successfully loaded.
0 Rows not loaded due to data errors.
74225 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Total logical records skipped: 0
Total logical records read: 74256
Total logical records rejected: 0
Total logical records discarded: 1
Sample CTL File
OPTIONS (ERRORS=9999999999, SILENT=(FEEDBACK), ROWS=1000)
load data
into table test_HD
append
when (038:039) = 'HD'
(
record_sequence_no POSITION(029:037) DECIMAL EXTERNAL
,line_type POSITION(040:040) CHAR
,header_record_type POSITION(041:041) CHAR
,enquiry_telephone_number POSITION(042:052) CHAR
,total_amt_last_bill POSITION(053:066) DECIMAL EXTERNAL
,total_payment_received POSITION(067:080) DECIMAL EXTERNAL
)
into table test_AS
append
when (038:039) = 'AS'
(
record_sequence_no POSITION(029:037) DECIMAL EXTERNAL
,line_type POSITION(040:040) CHAR
,girn POSITION(041:047) CHAR
,charge_desc POSITION(048:077) CHAR
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这不是一个错误。日志告诉您 72K 记录没有加载到任何地方，因为它们不满足您的任何 WHEN 子句。
换句话说，在 74256 条记录中，有 31 条在第 38 和 39 位有“HD”或“AS”，其余则没有。您还让它跳过一行（也许第一行是标题行）。
如果您尝试仅加载“HD”和“AS”记录，那么这是一个成功的结果。
It's not an error. The log is telling you that 72K records were not loaded anywhere because they did not satisfy any of your WHEN clauses.
In other words, out of 74256 records, 31 had 'HD' or 'AS' in positions 38 and 39, and the rest did not. You also had it skip one line (perhaps the first line is a header row).
That's a successful result if you were trying to load just the 'HD' and 'AS' records.