sqlloader case statement variable number of columns

Question

I have a table which is to be populated via sqlloader. Is it possible to employ logic in a ctl file so that it can cater for files which have different numbers of fields? e.g.

Table = field1 field2 field3 field4

File format 1 = field1 field2 field4

File format 2 = field 1 field2 field3 field4

Would it be easiest to switch between different .ctl files based on file name (e.g. make the one with 4 fields a distinctly different name to one with 3), or can sqlloader 'count' the number of fields it has been supplied and then apply them to the table logically though a case statement?

One further point is that all records within the file with conform to one format or the other, it will not vary from row to row which will hopefully make this task much easier.

Mick Mnemonic Mick Mnemonic · Accepted Answer · 2015-03-26T21:37:32

You could perhaps do this by using the BOUNDFILLER field type for the last two columns in your file. An example control file could look like this:

load data characterset WE8ISO8859P15
infile 'mydata.dat'
badfile 'mydata.bad'
discardfile 'mydata.dsc'
append
into table mytable fields terminated by whitespace trailing nullcols
(
  field1,
  field2,
  bind1 BOUNDFILLER,
  bind2 BOUNDFILLER,
  -- if bind2 is present, use value of bind1, else set to null
  field3 "nvl2(:bind2, :bind1, null)",
  -- if bind2 is null, use value of bind1
  field4 "nvl(:bind2, :bind1)"
)

So, you would define six fields in your control file:

The first two as "normal" fields you always expect to see in the file (field1 and field2)
The next two as local BOUNDFILLER variables you want to use as input to expressions (bind1 and bind2)
The last two as fields you will populate based on an expression (field3 and field4). These will not map to any actual columns in the input file.

The trailing nullcols option should populate bind2 as NULL even if your input file only has three columns. This approach will only work if you never expect any NULL values in the last column in the four-column input files.

I haven't tested this but it might be worth a try.

sqlloader case statement variable number of columns

1 Answers