Oracle Sql*loader Detailed (4) field List

In the previous article, we introduced the most important document in Sql*loader--control file, this article is about the most important part of the control file-the field list, the role of the field list is to the data file records and the database table columns, the following is an example of the field list, In this article we will explain what they mean.

.
.
.
1  (HireDate  sysdate,
2     deptno  POSITION (1:2)  INTEGER EXTERNAL (2)
              Nullif deptno= Blanks,
3       job   POSITION (7:14)  CHAR  terminated by whitespace nullif  " 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 (Wuyi) CHAR  terminated by whitespace
              " To_number (: Sal, ' $99,999.99 ') ",
4      comm   INTEGER EXTERNAL  enclosed by ' (' and '% '
              ": Comm * 100 "
    )

Specify the corresponding relationship between columns and fields

We know that Sql*loader's job is to load the records in the data file into a database table, so be sure to have the corresponding relationship between the record field of the data file and the columns of the database table, so in the field list of the control file, we first have to configure the relationship. Note here that all columns of the table are not required to appear in the field list, columns that do not appear, and Sql*loader are automatically populated with null.

But there is a special field, called the Parse field (filter identifier), it does not match the table column, its main role is to give the when statement to provide conditional judgment, as follows:

Into TABLE dept when 
   recid = 1 
   (recid  filler POSITION (1:1)  INTEGER EXTERNAL,
    deptno POSITION (3:4) C6/>integer EXTERNAL, 
    dname  POSITION (8:21) CHAR into the 
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)

Specify Location (POSITION)

We know that records are stored in bytes in data files, and if each field size is known, then we can use the position phrase to specify the byte position of the field in the record, with the following syntax:

Here are some examples:

ename  POSITION (1:20)  CHAR 
empno  POSITION (22-26) INTEGER EXTERNAL 
allow POSITION  (*+2)   INTEGER EXTERNAL terminated by "/"

In the example above, the 1~20 byte corresponds to the ename column, the 22~26 byte corresponds to the Empno column, * represents the beginning of the last byte of the previous field (that is, 27), so the *+2=29, that is, all bytes that start with the 29th byte until the delimiter '/' is encountered are allow columns.