Oracle SQL Loader 中的映射字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18299619/
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
Mapping fields in Oracle SQL Loader
提问by user2693748
When loading an external csv with Oracle SQL Loader is there a way to map the fields directly to each other in the control file?
使用 Oracle SQL Loader 加载外部 csv 时，是否可以将控制文件中的字段直接相互映射？
At the moment I'm doing a simple loading, so the position of the source fields is important. Is there a way to do it otherwise? So instead of:
目前我正在做一个简单的加载，所以源字段的位置很重要。有没有办法做到这一点？所以而不是：
load data
into table1
fields terminated by "," optionally enclosed by '"'
(destination_field1, destination_field2, destination_field3)
do something like:
做类似的事情：
load data
into table1
fields terminated by "," optionally enclosed by '"'
(
source_field2 => destination_field1,
source_field1 => destination_field2,
source_field3 => destination_field3
)
Edit:
编辑：
The main reason is that the order of the columns in the source file can change, therefore I can't be sure which field will be the first, second, etc.
主要原因是源文件中列的顺序可以更改，因此我无法确定哪个字段将是第一个、第二个等。
回答by Egor Skriptunoff
You can include any data processing by means of Oracle functions in your control file.
E.g., this code swaps columns 1 and 2 and additionally converts source_field2to number, silently replacing wrong values to nulls:
您可以在控制文件中包含通过 Oracle 函数进行的任何数据处理。
例如，这段代码交换了第 1 列和第 2 列，并另外转换source_field2为数字，默默地将错误的值替换为空值：
load data
append
into table SCHEMA.TABLE
fields terminated by ';' optionally enclosed by '"'
trailing nullcols
(
source_field1 BOUNDFILLER,
source_field2 BOUNDFILLER,
source_field3 BOUNDFILLER,
destination_field1 "to_number(regexp_substr(:source_field2, '^[-0-9,]*'),'9999999999D999','NLS_NUMERIC_CHARACTERS='', ''')",
destination_field2 ":source_field1",
destination_field3 ":source_field3"
)
