Oracle SQL Loader中的映射字段 [英] Mapping fields in Oracle SQL Loader
问题描述
使用Oracle SQL Loader加载外部csv时,是否可以将控制文件中的字段直接相互映射?
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?
目前,我正在执行简单的加载,因此源字段的位置很重要.是否有其他方法可以做到?因此,代替:
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)
做类似的事情:
load data
into table1
fields terminated by "," optionally enclosed by '"'
(
source_field2 => destination_field1,
source_field1 => destination_field2,
source_field3 => destination_field3
)
主要原因是源文件中列的顺序可以更改,因此我不确定哪个字段将是第一个,第二个,等等.
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.
推荐答案
您可以在控制文件中包括通过Oracle函数进行的任何数据处理.
例如,此代码交换第1列和第2列,并另外将 source_field2
转换为数字,将错误的值静默替换为null:
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_field2
to number, silently replacing wrong values to nulls:
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"
)
这篇关于Oracle SQL Loader中的映射字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!