Database Reference
In-Depth Information
The resulting
DEPT
table will look like this:
EODA@ORA12CR1> select * from dept;
DEPTNO DNAME LOC LAST_UPDA
---------- -------------- ------------- ---------
10 Sales Virginia 01-MAY-14
20 Accounting Virginia 21-JUN-14
30 Consulting Virginia 05-JAN-13
40 Finance Virginia 15-MAR-14
It is that easy. Just supply the format in the control file and SQLLDR will convert the date for us. In some cases, it
might be appropriate to use a more powerful SQL function. For example, your input file might contain dates in many
different formats: sometimes with the time component, sometimes without; sometimes in
DD-MON-YYYY
format;
sometimes in
DD/MM/YYYY
format; and so on. You'll learn in the next section how to use functions in SQLLDR to
overcome these challenges.
How Do I Load Data Using Functions?
In this section, you'll see how to refer to functions while loading data. Bear in mind, however, that the use of such
functions (including database sequences) requires the SQL engine, and hence won't work in a direct path load.
Using functions in SQLLDR is very easy once you understand how SQLLDR builds its
INSERT
statement. To have
a function applied to a field in a SQLLDR script, simply add it to the control file in double quotes. For example, say you
have the
DEPT
table from earlier, and you would like to make sure the data being loaded is in uppercase. You could use
the following control file to load it:
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10,Sales,Virginia,1/5/2014
20,Accounting,Virginia,21/6/2014
30,Consulting,Virginia,5/1/2013
40,Finance,Virginia,15/3/2014
