CODEWITHSUNDEEP
sqloracle-databasemappingsql-loader
user2693748
user2693748

Reputation: 21

Mapping fields in Oracle SQL Loader

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)

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.

Upvotes: 2

Views: 13552

Answers (1)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23767

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"
)

Upvotes: 4

Related Questions