Specifying the CHARACTERSET
parameter tells SQL*Loader the character set of the input data file. The default character set for all data files, if the CHARACTERSET
parameter is not specified, is the session character set defined by the NLS_LANG
parameter. Only character data (fields in the SQL*Loader data types CHAR,
VARCHAR,
VARCHARC,
numeric EXTERNAL
, and the datetime and interval data types) is affected by the character set of the data file.
The CHARACTERSET
syntax is as follows:
CHARACTERSET char_set_name
The char_set_name
variable specifies the character set name. Normally, the specified name must be the name of an Oracle-supported character set.
For UTF-16 Unicode encoding, use the name UTF16 rather than AL16UTF16. AL16UTF16, which is the supported Oracle character set name for UTF-16 encoded data, is only for UTF-16 data that is in big-endian byte order. However, because you are allowed to set up data using the byte order of the system where you create the data file, the data in the data file can be either big-endian or little-endian. Therefore, a different character set name (UTF16) is used. The character set name AL16UTF16 is also supported. But if you specify AL16UTF16 for a data file that has little-endian byte order, then SQL*Loader issues a warning message and processes the data file as little-endian.
The CHARACTERSET
parameter can be specified for primary data files and also for LOBFILEs and SDFs. All primary data files are assumed to be in the same character set. A CHARACTERSET
parameter specified before the INFILE
parameter applies to the entire list of primary data files. If the CHARACTERSET
parameter is specified for primary data files, then the specified value will also be used as the default for LOBFILEs and SDFs. This default setting can be overridden by specifying the CHARACTERSET
parameter with the LOBFILE or SDF specification.
The character set specified with the CHARACTERSET
parameter does not apply to data specified with the INFILE
clause in the control file. The control file is always processed using the character set specified for your session by the NLS_LANG
parameter. Therefore, to load data in a character set other than the one specified for your session by the NLS_LANG
parameter, you must place the data in a separate data file.
Oracle Database Globalization Support Guide for more information about the names of the supported character sets
Case study 11, Loading Data in the Unicode Character Set, for an example of loading a data file that contains little-endian UTF-16 encoded data. (See "SQL*Loader Case Studies" for information on how to access case studies.)