Database Reference
In-Depth Information
Here, once SQLLDR encountered a tab, it output a value. Hence,
10
is assigned to
DEPTNO
, and
DNAME
gets
NULL
since there is no data between the first tab and the next occurrence of a tab.
Sales
gets assigned to
LOC
.
This is the intended behavior of
TERMINATED BY WHITESPACE
and
TERMINATED BY <character>
. The one that is
more appropriate to use will be dictated by the input data and how you need it to be interpreted.
Lastly, when loading delimited data such as this, it is very common to want to skip over various columns in the
input record. For example, you might want to load fields 1, 3, and 5, skipping columns 2 and 4. To do this, SQLLDR
provides the
FILLER
keyword. This allows you to map a column in an input record, but not put it into the database.
For example, given the
DEPT
table and the last control file from earlier, we can modify the control file to load the data
correctly (skipping over the tabs) using the
FILLER
keyword:
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY x'09'
(DEPTNO,
dummy1 filler,
DNAME,
dummy2 filler,
LOC)
BEGINDATA
10 Sales Virginia
The resulting
DEPT
table is now as follows:
EODA@ORA12CR1> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 Sales Virginia
How Do I Load Fixed Format Data?
Often, you have a flat file generated from some external system, and this file is a fixed-length file with positional data.
For example, the
NAME
field is in bytes 1 to 10, the
ADDRESS
field is in bytes 11 to 35, and so on. We will look at how
SQLLDR can import this kind of data for us.
This fixed-width, positional data is the optimal data format for SQLLDR to load. It will be the fastest way to
process, as the input data stream is somewhat trivial to parse. SQLLDR will have stored fixed-byte offsets and lengths
into data records, and extracting a given field is very simple. If you have an extremely large volume of data to load,
converting it to a fixed position format is generally the best approach. The downside to a fixed-width file is, of course,
that it can be much larger than a simple, delimited file format.
To load fixed-width positional data, you will use the
POSITION
keyword in the control file, for example:
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
DNAME position(3:16),
LOC position(17:29)
)
BEGINDATA
10Accounting Virginia,USA
