set termout offrem host write sys$output "Building case 4 demonstration tables.  Please wait"drop table emp;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);exit

-- NAME-- ulcase4.ctl - SQL*Loader Case Study 4: Loading Combined Physical Records---- DESCRIPTION-- This case study demonstrates the following:-- Combining multiple physical records to form one logical-- record with CONTINUEIF.---- Inserting negative numbers.---- Using REPLACE to indicate that the table should be emptied-- before the new data is inserted.---- Specifying a discard file in the control file using DISCARDFILE.---- Specifying a maximum number of discards using DISCARDMAX.---- Rejecting records due to duplicate values in a unique index-- or due to invalid data values.---- TO RUN THIS CASE STUDY:-- 1. Before executing this control file, log in to SQL*Plus as--    scott/tiger. Enter @ulcase4 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=ulcase4.ctl LOG=ulcase4.log---- NOTES ABOUT THIS CONTROL FILE-- DISCARDFILE specifies a discard file named ulcase4.dsc.---- DISCARDMAX specifies a maximum of 999 discards allowed before-- terminating the run. For all practical purposes, this allows-- all discards for this test case. In real-world situations,-- there may well be more than 999 discarded records.---- REPLACE specifies that if there is data in the table being loaded,-- then SQL*Loader should delete that data before loading new data.---- CONTINUEIF specifies that if an asterisk is found in column 1-- of the current record, then the next physical record after that-- record should be appended to it from the logical record. Note that-- column 1 in each physical record should then contain either an-- asterisk or a nondata value.---- The data file (ulcase4.dat) for this case study shows asterisks-- in the first position and, though not visible, a newline character-- is in position 20. Note that clark‘s commission is -10, and-- SQL*Loader loads the value, converting it to a negative number.---- The resulting log file will show that the last two records are-- rejected, given two assumptions. If a unique index is created on-- column empno, then the record for chin will be rejected because-- his empno is identical to chan‘s. If empno is defined as NOT NULL,-- then chen‘s record will be rejected because it has no value for-- empno.--LOAD DATAINFILE "ulcase4.dat"DISCARDFILE "ulcase4.dsc"DISCARDMAX 999REPLACECONTINUEIF (1) = ‘*‘INTO TABLE EMP( EMPNO    POSITION(01:04) INTEGER EXTERNAL,  ENAME    POSITION(06:15) CHAR,  JOB      POSITION(17:25) CHAR,  MGR      POSITION(27:30) INTEGER EXTERNAL,  SAL      POSITION(32:39) DECIMAL EXTERNAL,  COMM     POSITION(41:48) DECIMAL EXTERNAL,  DEPTNO   POSITION(50:51) INTEGER EXTERNAL,  HIREDATE POSITION(52:60) INTEGER EXTERNAL)

*7782 CLARK      MA NAGER   7839  2572.50   -10    2512-NOV-85*7839 KING       PR ESIDENT       5500.00          2505-APR-83*7934 MILLER     CL ERK     7782   920.00          2508-MAY-80*7566 JONES      MA NAGER   7839  3123.75          2517-JUL-85*7499 ALLEN      SA LESMAN  7698  1600.00   300.00 25 3-JUN-84*7654 MARTIN     SA LESMAN  7698  1312.50  1400.00 2521-DEC-85*7658 CHAN       AN ALYST   7566  3450.00          2516-FEB-84*     CHEN       AN ALYST   7566  3450.00          2516-FEB-84*7658 CHIN       AN ALYST   7566  3450.00          2516-FEB-84

SQL> select * from emp;EMPNO ENAME  JOB       MGR   HIREDATE  SAL     COMM DEPTNO----- ------ --------- ----- --------- ------- ----- ------ 7782 CLARK  MANAGER   7839  12-NOV-85 2573     -10  25 7839 KING   PRESIDENT       05-APR-83 5500          25 7934 MILLER CLERK      7782 08-MAY-80  920          25   7566 JONES  MANAGER    7839 17-JUL-85 3124          25 
 
 7499 ALLEN  SALESMAN   7698 03-JUN-84 1600     300  25 7654 MARTIN SALESMAN   7698 21-DEC-85 1313    1400  25 7658 CHAN   ANALYST    7566 16-FEB-84 3450          257 rows selected.

SQL*Loader: Release 11.2.0.1.0 - Production on Thu Sep 18 23:41:36 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Control File:   ulcase4.ctlData File:      ulcase4.dat  Bad File:     ulcase4.bad  Discard File: ulcase4.dsc  (Allow 999 discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array:     64 rows, maximum of 256000 bytesContinuation:   1:1 = 0X2a(character ‘*‘), in current physical recordPath 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            JOB                                 17:25     9           CHARACTER            MGR                                 27:30     4           CHARACTER            SAL                                 32:39     8           CHARACTER            COMM                                41:48     8           CHARACTER            DEPTNO                              50:51     2           CHARACTER            HIREDATE                            52:60     9           CHARACTER            Record 8: Rejected - Error on table EMP, column EMPNO.ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO")Record 9: Rejected - Error on table EMP.ORA-00001: unique constraint (SCOTT.EMPIX) violatedTable EMP:  7 Rows successfully loaded.  2 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.Space allocated for bind array:                   4608 bytes(64 rows)Read   buffer bytes: 1048576Total logical records skipped:          0Total logical records read:             9Total logical records rejected:         2Total logical records discarded:        0Run began on Thu Sep 18 23:41:36 2014Run ended on Thu Sep 18 23:41:36 2014Elapsed time was:     00:00:00.29CPU time was:         00:00:00.07

*     CHEN       AN ALYST   7566  3450.00          2516-FEB-84*7658 CHIN       AN ALYST   7566  3450.00          2516-FEB-84