Oracle Sql*loader Detailed (4) field List

Source: Internet
Author: User

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)",
              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, 

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 
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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.