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
EXTERNAL, and the datetime and interval data types) is affected by the character set of the data file.
CHARACTERSET syntax is as follows:
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.
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.)