SQL * Loader Expert

Now we would continue out discussion on SQL * loader .

Have a look at the following .ctl file

LOAD DATA
INFILE *
APPEND
INTO TABLE test_emp
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr, hiredate DATE "DD-Month-YYYY",
sal, comm, deptno CHAR TERMINATED BY ':',
projno, loadseq SEQUENCE(MAX,1))
BEGINDATA
1111,"aaaaa","Salesman",7698, 20-February-2001, 1600.00, 300.00, 30:103
2222,"bbbbb","Salesman",7698, 28-September-2000, 1312.50, 1400.00, 30:103
3333, "ccccc", "Analyst", 7566, 03-May-1999, 3450,, 20:101

Things to note:

1. The data to be loaded is just after the BEGINDATA

2. SEQUENCE(MAX,1)) would all a sequence number to every record loaded.

3. hiredate DATE "DD-Month-YYYY" where the hiredate field with be formatted as "DD-Month-YYYY".

4. Check for the feild deptno CHAR TERMINATED BY ':', where the deptno is extracted before the ":" as 30 for 30:103.

You can try the same with

table name test_emp

empno varchar2
ename varchar2
job varchar2
mgr varchar2
hiredate datetime
sal number
comm varchar2
deptno varchar2
projno varchar2
loadseq number

Try to create a batch file from the prevoius post.