Distinguishing Different Input Record Formats

A single data file might contain records in a variety of formats. Consider the following data, in which emp and dept records are intermixed:

1 50   Manufacturing       — DEPT record 
2 1119 Smith      50       — EMP record 
2 1120 Snyder     50 
1 60   Shipping 
2 1121 Stevens    60

A record ID field distinguishes between the two formats. Department records have a 1 in the first column, while employee records have a 2. The following control file uses exact positioning to load this data:

INTO TABLE dept 
   WHEN recid = 1 
   (recid  FILLER POSITION(1:1)  INTEGER EXTERNAL,
    deptno POSITION(3:4)  INTEGER EXTERNAL, 
    dname  POSITION(8:21) CHAR) 
INTO TABLE emp 
   WHEN recid <> 1 
   (recid  FILLER POSITION(1:1)   INTEGER EXTERNAL,
    empno  POSITION(3:6)   INTEGER EXTERNAL, 
    ename  POSITION(8:17)  CHAR, 
    deptno POSITION(19:20) INTEGER EXTERNAL)

Relative Positioning Based on the POSITION Parameter

The records in the previous example could also be loaded as delimited data. In this case, however, it is necessary to use the POSITION parameter. The following control file could be used:

INTO TABLE dept 
   WHEN recid = 1 
   (recid  FILLER INTEGER EXTERNAL TERMINATED BY WHITESPACE, 
    deptno INTEGER EXTERNAL TERMINATED BY WHITESPACE, 
    dname  CHAR TERMINATED BY WHITESPACE) 
INTO TABLE emp 
   WHEN recid <> 1 
   (recid  FILLER POSITION(1) INTEGER EXTERNAL TERMINATED BY ' ', 
    empno  INTEGER EXTERNAL TERMINATED BY ' ' 
    ename  CHAR TERMINATED BY WHITESPACE, 
    deptno INTEGER EXTERNAL TERMINATED BY ' ')

The POSITION parameter in the second INTO TABLE clause is necessary to load this data correctly. It causes field scanning to start over at column 1 when checking for data that matches the second format. Without it, SQL*Loader would look for the recid field after dname.