Field List Contents

The field-list portion of a SQL*Loader control file provides information about fields being loaded, such as position, data type, conditions, and delimiters.

Example 10-1 shows the field list section of the sample control file that was introduced in SQL*Loader Control File Reference.

Example 10-1 Field List Section of Sample Control File

.
.
.
1  (hiredate  SYSDATE,
2     deptno  POSITION(1:2)  INTEGER EXTERNAL(2)
              NULLIF deptno=BLANKS,
3       job   POSITION(7:14)  CHAR  TERMINATED BY WHITESPACE
              NULLIF job=BLANKS  "UPPER(:job)",
       mgr    POSITION(28:31) INTEGER EXTERNAL 
              TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
       ename  POSITION(34:41) CHAR 
              TERMINATED BY WHITESPACE  "UPPER(:ename)",
       empno  POSITION(45) INTEGER EXTERNAL 
              TERMINATED BY WHITESPACE,
       sal    POSITION(51) CHAR  TERMINATED BY WHITESPACE
              "TO_NUMBER(:sal,'$99,999.99')",
4      comm   INTEGER EXTERNAL  ENCLOSED BY '(' AND '%'
              ":comm * 100"
    )

In this sample control file, the numbers that appear to the left would not appear in a real control file. They are keyed in this sample to the explanatory notes in the following list:

SYSDATE sets the column to the current system date. See "Setting a Column to the Current Date ".
POSITION specifies the position of a data field. See "Specifying the Position of a Data Field".

INTEGER EXTERNAL is the data type for the field. See "Specifying the Data Type of a Data Field" and "Numeric EXTERNAL".

The NULLIF clause is one of the clauses that can be used to specify field conditions. See "Using the WHEN_ NULLIF_ and DEFAULTIF Clauses".

In this sample, the field is being compared to blanks, using the BLANKS parameter. See "Comparing Fields to BLANKS".
The TERMINATED BY WHITESPACE clause is one of the delimiters it is possible to specify for a field. See "Specifying Delimiters".
The ENCLOSED BY clause is another possible field delimiter. See "Specifying Delimiters".