set termout offrem host write sys$output "Building case 5 demonstration tables.  Please wait"drop table emp;drop table proj;create table emp       (empno number(4) not null,        ename char(10),        job char(9),        mgr number(4),        hiredate date,        sal number(7,2),        comm number(7,2),        deptno number(2));create unique index empix on emp(empno);create table proj        (empno number,         projno number);exit                        

-- Copyright (c) 1991, 2004 Oracle.  All rights reserved.-- NAME-- ulcase5.ctl - SQL*Loader Case Study 5: Loading Data Into Multiple Tables-- DESCRIPTION-- This case study demonstrates the following:-- Loading multiple tables.---- Using SQL*Loader to break down repeating groups in a flat file-- and to load the data into normalized tables. In this way, one-- file record may generate multiple database rows.---- Deriving multiple logical records from each physical record.---- Using a WHEN clause.---- Loading the same field (empno) into multiple tables.---- TO RUN THIS CASE STUDY:-- 1. Before executing this control file, log in to SQL*Plus as--    scott/tiger. Enter @ulcase5 to execute the SQL script for--    this case study. This prepares and populates tables and--    then returns you to the system prompt.---- 2. At the system prompt, invoke the case study as follows:-- sqlldr USERID=scott/tiger CONTROL=ulcase5.ctl LOG=ulcase5.log---- NOTES ABOUT THIS CONTROL FILE-- REPLACE specifies that if there is data in the tables to be-- loaded (emp and proj), SQL*loader should delete the data before-- loading new rows.---- Multiple INTO TABLE clauses load two tables, emp and proj.-- The same set of records is processed three times, using different-- combinations of columns each time to load table proj.---- WHEN loads only rows with nonblank project numbers. When projno-- is defined as columns 25...27, rows are inserted into proj only-- if there is a value in those columns.---- When projno is defined as columns 29...31, rows are inserted-- into proj only if there is a value in those columns.---- When projno is defined as columns 33...35, rows are inserted-- into proj only if there is a value in those columns.--LOAD DATAINFILE ‘ulcase5.dat‘BADFILE ‘ulcase5.bad‘DISCARDFILE ‘ulcase5.dsc‘REPLACEINTO TABLE EMP  (EMPNO    POSITION(1:4)   INTEGER EXTERNAL,   ENAME    POSITION(6:15)  CHAR,   DEPTNO   POSITION(17:18) CHAR,   MGR      POSITION(20:23) INTEGER EXTERNAL)INTO TABLE PROJ-- PROJ has two columns, both not null: EMPNO and PROJNOWHEN PROJNO != ‘   ‘  (EMPNO    POSITION(1:4)   INTEGER EXTERNAL,   PROJNO   POSITION(25:27) INTEGER EXTERNAL)   -- 1st projINTO TABLE PROJWHEN PROJNO != ‘   ‘  (EMPNO    POSITION(1:4)   INTEGER EXTERNAL,   PROJNO   POSITION(29:31) INTEGER EXTERNAL)   -- 2nd projINTO TABLE PROJWHEN PROJNO != ‘   ‘  (EMPNO    POSITION(1:4)   INTEGER EXTERNAL,   PROJNO   POSITION(33:35) INTEGER EXTERNAL)   -- 3rd proj                                                        

1234 BAKER      10 9999 101 102 1031234 JOKER      10 9999 777 888 9992664 YOUNG      20 2893 425 abc 1025321 OTOOLE     10 9999 321  55  402134 FARMER     20 4555 236 4562414 LITTLE     20 5634 236 456  406542 LEE        10 4532 102 321  142849 EDDS       xx 4555     294  404532 PERKINS    10 9999  401244 HUNT       11 3452 665 133 456 123 DOOLITTLE  12 9940         1321453 MACDONALD  25 5532     200

SQL> select * from emp;EMPNO ENAME     JOB         MGR HIREDATE      SAL  COMM DEPTNO----- ---------- --------- ----- --------- ------- ----- ------ 1234 BAKER                 9999                         10 5321 OTOOLE                9999                         10 2134 FARMER                4555                         20 2414 LITTLE                5634                         20 6542 LEE                   4532                         10 4532 PERKINS               9999                         10 1244 HUNT                  3452                         11  123 DOOLITTLE             9940                         12 1453 MACDONALD             5532                         259 rows selected.SQL> select * from proj order by 1;EMPNO PROJNO----- ------  123     132 1234     101 1234     103 1234     102 1244     456 1244     133 1244     665 1453     200 2134     236 2134     456 2414     456EMPNO PROJNO----- ------ 2414     236 2414      40 4532      40 5321      55 5321      40 5321     321 6542     102 6542      14 6542     32120 rows selected.

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Sep 19 00:20:39 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Control File:   ulcase5.ctlData File:      ulcase5.dat  Bad File:     ulcase5.bad  Discard File: ulcase5.dsc  (Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array:     64 rows, maximum of 256000 bytesContinuation:    none specifiedPath used:      ConventionalTable EMP, loaded from every logical record.Insert option in effect for this table: REPLACE   Column Name                  Position   Len  Term Encl Datatype------------------------------ ---------- ----- ---- ---- ---------------------EMPNO                                 1:4     4           CHARACTER            ENAME                                6:15    10           CHARACTER            DEPTNO                              17:18     2           CHARACTER            MGR                                 20:23     4           CHARACTER            Table PROJ, loaded when PROJNO != 0X202020(character ‘   ‘)Insert option in effect for this table: REPLACE   Column Name                  Position   Len  Term Encl Datatype------------------------------ ---------- ----- ---- ---- ---------------------EMPNO                                 1:4     4           CHARACTER            PROJNO                              25:27     3           CHARACTER            Table PROJ, loaded when PROJNO != 0X202020(character ‘   ‘)Insert option in effect for this table: REPLACE   Column Name                  Position   Len  Term Encl Datatype------------------------------ ---------- ----- ---- ---- ---------------------EMPNO                                 1:4     4           CHARACTER            PROJNO                              29:31     3           CHARACTER            Table PROJ, loaded when PROJNO != 0X202020(character ‘   ‘)Insert option in effect for this table: REPLACE   Column Name                  Position   Len  Term Encl Datatype------------------------------ ---------- ----- ---- ---- ---------------------EMPNO                                 1:4     4           CHARACTER            PROJNO                              33:35     3           CHARACTER            Record 2: Rejected - Error on table EMP.ORA-00001: unique constraint (SCOTT.EMPIX) violatedRecord 8: Rejected - Error on table EMP, column DEPTNO.ORA-01722: invalid numberRecord 3: Rejected - Error on table PROJ, column PROJNO.ORA-01722: invalid numberTable EMP:  9 Rows successfully loaded.  3 Rows not loaded due to data errors.  0 Rows not loaded because all WHEN clauses were failed.  0 Rows not loaded because all fields were null.Table PROJ:  7 Rows successfully loaded.  2 Rows not loaded due to data errors.  3 Rows not loaded because all WHEN clauses were failed.  0 Rows not loaded because all fields were null.Table PROJ:  7 Rows successfully loaded.  3 Rows not loaded due to data errors.  2 Rows not loaded because all WHEN clauses were failed.  0 Rows not loaded because all fields were null.Table PROJ:  6 Rows successfully loaded.  3 Rows not loaded due to data errors.  3 Rows not loaded because all WHEN clauses were failed.  0 Rows not loaded because all fields were null.Space allocated for bind array:                   4096 bytes(64 rows)Read   buffer bytes: 1048576Total logical records skipped:          0Total logical records read:            12Total logical records rejected:         3Total logical records discarded:        0Run began on Fri Sep 19 00:20:39 2014Run ended on Fri Sep 19 00:20:40 2014Elapsed time was:     00:00:01.30CPU time was:         00:00:00.70

1234 JOKER      10 9999 777 888 9992849 EDDS       xx 4555     294  402664 YOUNG      20 2893 425 abc 102