Data Loading and Unloading - Expert Oracle Database Architecture - page 733

It then prepares and binds the inputs to this statement, and executes it. So, pretty much anything you can think
of doing in SQL, you can incorporate into your SQLLDR scripts. With the addition of the CASE statement in SQL, doing
this can be extremely powerful and easy. For example, say your input file could have dates in the following formats:

•

HH24:MI:SS : Just a time; the date should default to the first day of the current month.

•

DD/MM/YYYY : Just a date; the time should default to midnight.

HH24:MI:SS DD/MM/YYYY : The date and time are both explicitly supplied.
You could use a control file like this:

•

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED
"case
when length(:last_updated) > 9
then to_date(:last_updated,'hh24:mi:ss dd/mm/yyyy')
when instr(:last_updated,':') > 0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'dd/mm/yyyy')
end"
)
BEGINDATA
10,Sales,Virginia,12:03:03 17/10/2014
20,Accounting,Virginia,02:23:54
30,Consulting,Virginia,01:24:00 21/10/2014
40,Finance,Virginia,17/8/2014

This results in the following:

EODA@ORA12CR1> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
EODA@ORA12CR1> select deptno, dname, loc, last_updated from dept;
DEPTNO DNAME LOC LAST_UPDATED
------ ------------------------------ ------------- --------------------
10 SALES VIRGINIA 17-oct-2014 12:03:03
20 ACCOUNTING VIRGINIA 01-mar-2014 02:23:54
30 CONSULTING VIRGINIA 21-oct-2014 01:24:00
40 FINANCE VIRGINIA 17-aug-2014 00:00:00

Now, one of three date formats will be applied to the input character string (notice that you are not loading a
DATE anymore; you are just loading a string). The CASE function will look at the length and the contents of the string to
determine which of the masks it should use.