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

Another popular format is tab-delimited data , which is data separated by tabs rather than commas. There are two
ways to load this data using the TERMINATED BY clause:
•

TERMINATED BY X'09' (the tab character using hexadecimal format; in ASCII, 9 is a
tab character)

TERMINATED BY WHITESPACE
The two are very different in implementation, as the following shows. Using the DEPT table from earlier, we'll load
using this control file:

•

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY WHITESPACE
(DEPTNO, DNAME, LOC)
BEGINDATA
10 Sales Virginia

It is not readily visible on the page, but there are two tabs between each piece of data here. The data line is
actually as follows, where the \t is the universally recognized tab escape sequence.

10 \t\t Sales \t\t Virginia

When you use this control file with the TERMINATED BY WHITESPACE clause as previously, the resulting data in the
table DEPT is this:

EODA@ORA12CR1> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 Sales Virginia

TERMINATED BY WHITESPACE parses the string by looking for the first occurrence of whitespace (tab, blank, or
newline), and then it continues until it finds the next non -whitespace character. Hence, when it parsed the data,
DEPTNO had 10 assigned to it, the two subsequent tabs were considered as whitespace, Sales was assigned to DNAME ,
and so on.
On the other hand, suppose you were to use FIELDS TERMINATED BY X'09' , as the following modified control
file does:

...
FIELDS TERMINATED BY X'09'
(DEPTNO, DNAME, LOC)
...

You would find DEPT loaded with the following data:

EODA@ORA12CR1> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 Sales