The SQLULDR2 improved the support of unloading in delimited and fix length text file. There are some difference for the SQL*Loader (sqlldr) control files. Here is a sample control file for delimited format.
LOAD DATA
INFILE 'uldrdata.txt' "STR X'0a'"
INSERT INTO TABLE emp_his
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS
(
EMPNO CHAR(4),
ENAME CHAR(10),
JOB CHAR(9),
MGR CHAR(4),
HIREDATE DATE "YYYY-MM-DD HH24:MI:SS",
SAL CHAR(7),
COMM CHAR(7),
DEPTNO CHAR(2)
)
When you feel hard to choose the field and record separators, why not try the fix length format? Here is a sample control file for fix length format.
LOAD DATA
INFILE 'uldrdata.txt' "FIX 63"
INSERT INTO TABLE emp_his
FIELDS TERMINATED BY X'20' TRAILING NULLCOLS
(
EMPNO POSITION(1:4) CHAR(4),
ENAME POSITION(5:14) CHAR(10),
JOB POSITION(15:23) CHAR(9),
MGR POSITION(24:27) CHAR(4),
HIREDATE POSITION(28:46) DATE "YYYY-MM-DD HH24:MI:SS",
SAL POSITION(47:53) CHAR(7),
COMM POSITION(54:60) CHAR(7),
DEPTNO POSITION(61:62) CHAR(2)
)
As we tested previously, fix length format can lead to better data load performance.
Comments (1)
Have you read any of Cem Kaner's books? He is such a great author, I have read all of his books and learned so much from them. I was lucky enough to see him give a speech a few years ago on his methodology. He is as good a speaker as he is an author. Do you know of any other authors of Kaner's reputation?
Posted by Golda Jennins | January 3, 2010 4:10 AM