Load Data into more than one table using SQl*Loader

Load Data into more than one table using SQl*Loader

Assume this is the format of the 2 tables

Table 1 :

CREATE TABLE XX_EMP
(
 record_type NUMBER,
 eno  NUMBER,
 ename  VARCHAR2(20),
 deptid NUMBER,
 phone NUMBER,
 doj DATE
);

Table 2 :

CREATE TABLE XX_DEPT
(
 record_type NUMBER,
 deptid NUMBER,
 dname VARCHAR2(20)
);

Requirement :

. Load data into xx_emp,xx_dept tables from single data file.

. If phone is NULL fill it with 'NA'

. doj column must be in "DD-MON-YYYY" format

Solution :

Control File :

LOAD DATA
INFILE '$1'
APPEND
INTO TABLE XX_EMP
WHEN record_type = '100'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
RECORD_TYPE POSITION(1),
eno,
ename,
deptid,
phone "decode(:phone,null,'NA')",
doj DATE "DD-MON-YYYY"
)
INTO TABLE XX_DEPT
WHEN record_type = '200'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
RECORD_TYPE POSITION(1),
deptid,
dname
)

Save the above file as "main.ctl" and place in the bin folder of Custom module Top, eg : $XXPO_TOP/bin

Data File :

100|1001|Nag|501|12345|30-MAY-2012
200|10|EEE
100|1002|Tom|502||10-JUN-2012

Save above file as "info.dat"

Note : POSITION(1) takes to the starting of the row data

Register the following as concurrent program with one parameter as data path : give the data file path in this parameter.

Now

select * from xx_emp;

select * from xx_dept;

Thanks!!