Oracle Sql*loader Detailed (3) control file

The control file is the most important file in Sql*loader, which is a text file that defines the location of the data file, the format of the data, and the behavior of configuring the data loading process, and specifies the control file in Sqlldr with the controlling parameter.

Configure command line arguments in control files (OPTIONS)

In the previous article, we said that command-line arguments can be configured in three places, one of which is that you can use the options of the control file (which is also the most common way), as follows:

OPTIONS (Direct=true, silent= (ERRORS, FEEDBACK))

For more command-line arguments, see the previous article.

Configuration data file (INFILE)

Using infile, you can configure one or more data files with the following syntax:

If the data is contained in the control file itself, it is represented by *, such as:

INFILE  *

At the end of the control file, start with Begindata, followed by the data:

Begindata 
Data

What if you have multiple data files? It's simple, as long as multiple infile are available, and you can specify individual error files and discard files for each infile, as follows:

INFILE  mydat1.dat  badfile  mydat1.bad  discardfile  mydat1.dis INFILE mydat2.dat 
INFILE  mydat3.dat  discardfile  mydat3.dis 
INFILE  mydat4.dat  10 0

Character

If the character set of the data file is not the same as the character set of the database, Sql*loader automatically converts the character set of the data file to the character set of the database, provided that the character set of the database is a superset of the character set of the data file.

The character set of the database can be queried by the following SQL statement:

SELECT * from Nls_database_parameters;

The character set of the data file can be configured by the CharacterSet parameter in the control file, and its syntax is as follows:

If the CharacterSet parameter is not set, the character set of the data file is set by the Nls_lang of the operating system.
The data types affected by the character set are: Char,varchar,numeric EXTERNAL, datetime, Interval

There is also a character set to pay special attention to the control of the file itself is the character set (can only be set by the Nls_lang), if the control of the file's character set and data files, will be converted to the character set of the data file, but this is very easy to make mistakes (especially the separator), Generally, the nls_lang,characterset (if any) is set to the same as the database character set.