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.