[Oracle] Sql*loader Detailed Usage Tutorials (4)-field list

In the previous article, we introduced the most important file in Sql*loader-control file, and this article to introduce the most important part of the control file-the field list, the role of the field list is to the data file records and tables in the database corresponding to the column, here 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 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 ($) 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 * + "    )  

Specify the correspondence between columns and fields

We know that Sql*loader's job is to load records from a data file into a database table, so be sure to have a correspondence between the record fields 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 this relationship. It is important to note that all the columns of the table do not need to appear in the field list, the columns that do not appear, and sql*loader are automatically filled with null.

But there is a special field called the Parse field (identified by the filter), which does not match the table's columns, and its main function is to give the when statement the basis for conditional judgment, as follows:

Into TABLE dept when    recid = 1    (recid  FILLER POSITION (1:1)  INTEGER EXTERNAL,    deptno POSITION (3:4) C5/>integer EXTERNAL,     dname  POSITION (8:21) CHAR) to 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 the records are stored in bytes in the data file, and if each field size of the record is known, we can use the position sentence 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 above example, the 1~20 byte corresponds to the ename column, the 22~26 byte corresponds to the Empno column, and the * represents the beginning of the last byte of the previous field (that is, 27), so *+2=29, that is, starting with the 29th byte, until the delimiter '/' is encountered, all bytes are in the Allow column.

Data type

Sql*loader read the data file field according to the data type defined in the control file, and then send it to the corresponding column in the database table, it is important to note that the data type defined by the control file does not need to be the same as the corresponding table column in the database, because Sql*loader is automatically converted, Including the conversion of character sets, of course, you have to ensure that they can be converted, or will be error.

The data type of the control file is divided into two types, portable and non-portable, and the so-called portable data types are independent of the specific platform, and the non-portable is just the opposite.

The non-portable data types are: Integer (n), smallint, float, double, byteint, zoned,decimal,vargraphic, varchar, varraw, long Varraw,

In general, I use portable data types, so let's focus on the portable data types below:

CHAR

The most common and default data type is the following syntax:

Length indicates the maximum size of char, or 256 if not specified, and must be separated from the Char section of the database, Sql*loader char is a variable-length data type, somewhat similar to the database varchar.

Datatime

• DATE

• TIME

• TIME WITH TIME ZONE

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

Interval

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

Numeric EXTERNAL

The data type of the numeric type is represented as a character, including ( INTEGER EXTERNAL, FLOAT EXTERNAL, DECIMAL EXTERNAL, ZONED EXTERNAL and), whose attributes are similar to char, and in practice it is generally used instead of the non-portable numeric data type.

Separator

CHAR, datetime, Interval, numeric EXTERNAL fields can be identified by using delimiters, and the syntax for delimiters is as follows:

Terminated by and enclosed by can be used alone, or can be used together, here are some examples:

TERMINATED by ', '                      a data string, enclosed by ' ', '                        a data string ' TERMINATED by ', ' enclosed by ' "'"      a data str ing ", enclosed by ' (' and ') '                

Field condition settings (when, Nullif, defaultif)

NULLIF: If the qualifying is set to NULL, here is an example:

The blanks parameter indicates the meaning of whitespace (excluding tab), and the example above indicates that if the field is blanks, the field is null.

Sql*loader Generating Data

Sometimes we may want some data to be generated automatically during the loading of the data, and Sql*loader provides some parameters for generating the data.

CONSTANT

The value of the set column is constant and the syntax is as follows:

column_name CONSTANT  value

An expression

Set the value of the column to the value of the expression, with the following syntax:

column_name EXPRESSION "SQL string"

Current date

Set the value of the column to the current date with the following syntax:

column_name Sysdate

Sequence

The value of the Set column is a unique sequence number with the following syntax:

Example:

[Plain]View PlainCopyprint?

1. ID SEQUENCE (+)

It is important to note that sequence in the Oracle database cannot be used, which is really inconvenient.

[Oracle] Sql*loader Detailed Usage Tutorials (4)-field list