Database Reference
In-Depth Information
The control file that was generated by this shows the following (note that the numbers in parentheses in
bold
on
the right are not actually in the file; they are solely for reference purposes):
load data
(1)
infile 'emp.dat' "str x'7E0A'"
(2)
into table emp
(3)
replace
(4)
fields terminated by X'2c' enclosed by X'22'
(5)
(
(6)
EMPNO char(44),
(7)
ENAME char(20),
(8)
JOB char(18),
(9)
MGR char(44),
(10)
HIREDATE date 'ddmmyyyyhh24miss' ,
(11)
SAL char(44),
(12)
COMM char(44),
(13)
DEPTNO char(44),
(14)
)
(15)
The things to note about this control file are as follows:
STR
feature of SQLLDR. We can specify what character or string is used
to terminate a record. This allows us to load data with embedded newlines easily. The string
x'7E0A'
is simply a tilde followed by a newline.
•
Line (2): We use the
OPTIONALLY
ENCLOSED BY
, since we will be enclosing every single field after doubling any occurrence of the
enclosure character in the raw data.
•
Line (5): We use our separator character and enclosure character. We do not use
•
Line (11): We use a large numeric date format. This does two things: it avoids any NLS issues
with regard to the data, and it preserves the time component of the date field.
The raw data (
.dat
) file generated from the preceding code looks like this:
"7369","SMITH","CLERK","7902","17121980000000","800","","20"~
"7499","ALLEN","SALESMAN","7698","20021981000000","1600","300","30"~
"7521","WARD","SALESMAN","7698","22021981000000","1250","500","30"~
"7566","JONES","MANAGER","7839","02041981000000","2975","","20"~
"7654","MARTIN","SALESMAN","7698","28091981000000","1250","1400","30"~
"7698","BLAKE","MANAGER","7839","01051981000000","2850","","30"~
"7782","CLARK","MANAGER","7839","09061981000000","2450","","10"~
"7788","SCOTT","ANALYST","7566","19041987000000","3000","","20"~
"7839","KING","PRESIDENT","","17111981000000","5000","","10"~
"7844","TURNER","SALESMAN","7698","08091981000000","1500","0","30"~
"7876","ADAMS","CLERK","7788","23051987000000","1100","","20"~
"7900","JAMES","CLERK","7698","03121981000000","950","","30"~
"7902","FORD","ANALYST","7566","03121981000000","3000","","20"~
"7934","MILLER","CLERK","7782","23011982000000","1300","","10"~
