多个 infiles 的 Oracle Sql Loader 跳过选项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6521595/
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 Sql Loader skip option for multiple infiles
提问by reforrer
When using SQL Loader control file as following:
使用 SQL Loader 控制文件时，如下所示：
OPTIONS(**skip=1**,bindsize=1048576,rows=1024)
LOAD DATA
INFILE 'C:\Documents and Settings\FIRST.CSV'
INFILE 'C:\Documents and Settings\SECOND.CSV'
APPEND
INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
fields
)
it skips the header row for FIRST.CSV file, but it loads the header row from SECOND.CSV into Oracle table. My solution is to break this control file into two separate files. Any way to comply with one control file?
它会跳过 FIRST.CSV 文件的标题行，但会将 SECOND.CSV 中的标题行加载到 Oracle 表中。我的解决方案是将这个控制文件分成两个单独的文件。有什么方法可以遵守一个控制文件？
回答by Martin Schapendonk
You could do with one control file, but it would still require you to run sqlldr twice:
您可以使用一个控制文件，但它仍然需要您运行 sqlldr 两次：
Control file:
控制文件：
OPTIONS(skip=1,bindsize=1048576,rows=1024)
LOAD DATA
APPEND
INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
fields
)
And then run sqlldr like this:
然后像这样运行 sqlldr：
sqlldr control=control.ctl data=FIRST.CSV
sqlldr control=control.ctl data=SECOND.CSV
Another option that just occurred to me is that you may be able to check a record with a WHEN clause:
我刚刚想到的另一个选择是，您可以使用 WHEN 子句检查记录：
OPTIONS(bindsize=1048576,rows=1024)
LOAD DATA
INFILE 'C:\Documents and Settings\FIRST.CSV'
INFILE 'C:\Documents and Settings\SECOND.CSV'
APPEND
INTO TABLE table_name
WHEN (field1 <> 'ContentsOfField1InHeaderRow')
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
fields
)
If your header always contains fixed text, you could skip it based on content of (one of) the fields. Using WHEN may have an impact on performance though - depending on size of the files you may be better off with two calls to sqlldr.
如果您的标题始终包含固定文本，您可以根据（其中一个）字段的内容跳过它。但是，使用 WHEN 可能会对性能产生影响 - 根据文件的大小，两次调用 sqlldr 可能会更好。
