sqlldr - how to use if/then logic on a field?

Question

I am loading a particular field that has date values. However, some of them are not complete... for example the values look like this

START_DATE  

'2015-06-12'  
'2016-12-24'   
'2015-02'      <--- this is what causes an error  
'2016-01-03'

I have tried solving this by combining NULLIF with a LENGTH() function like so, but this is not allowed:

Start_date NULLIF LENGTH(:start_date)<10 to_date .....

this returns the error

Expecting positive integer or column name, found keyword length.

My main objective is to load dates that are of a proper format, and load NULL otherwise. What is the easiest way to do this within the ctl file? Can I avoid creating a custom function?

Answer 1

Say I have a table like this:

create table dateTable(START_DATE date)

and I need to load this file, where I want to insert NULL where the string does not match my pattern

'2016-12-28'
'2016-12-'
'2016-12-31'

I can add some logic in my ctl file to check the length of the string to load this way:

load data
infile dateTable.csv
into TABLE dateTable
fields enclosed by "'"
( START_DATE "to_date(case when length(:START_DATE) = 10 then :START_DATE end, 'yyyy-mm-dd')"
)

This simply checks the length of the string, but you can edit it anyway you need to build your own logic; notice that CASE gives NULL when no condition is matched, so this is equivalent to case when length(:START_DATE) = 10 then :START_DATE else NULL end.

This gives the following result:

SQL> select * from dateTable;

START_DATE
----------
28-DEC-16

31-DEC-16

Answer 2

In oracle, you can verify a string to make sure that is it valid date or not. Please Check IsDate function.