Database Reference
In-Depth Information
The ending position in the
POSITION
clause must be the absolute column position where the data ends. At times,
it can be easier to specify just the length of each field, especially if they are contiguous, as in the preceding example.
In this fashion, we would just have to tell SQLLDR the record starts at byte 1, and then specify the length of each
field. This will save us from having to compute start and stop byte offsets into the record, which can be hard at times.
In order to do this, we'll leave off the ending position and instead specify the
length
of each field in the fixed-length
record as follows:
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1) char(2),
DNAME position(*) char(14),
LOC position(*) char(13),
ENTIRE_LINE position(1) char(29)
)
BEGINDATA
10Accounting Virginia,USA
Here we had to tell SQLLDR only where the first field begins and its length. Each subsequent field starts where
the last one left off and continues for a specified length. It is not until the last field that we have to specify a position
again, since this field goes back to the beginning of the record.
How Do I Load Dates?
Loading dates using SQLLDR is fairly straightforward, but it seems to be a common point of confusion. You simply
need to use the
DATE
data type in the control file and specify the date mask to be used. This date mask is the same
mask you use with
TO_CHAR
and
TO_DATE
in the database. SQLLDR will apply this date mask to your data and load
it for you.
For example, let's say we alter our
DEPT
table again, as follows:
EODA@ORA12CR1> alter table dept add last_updated date;
Table altered.
We can load it with the following control file:
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME,
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
