Byte-length semantics are the default for all data files except those that use the UTF16 character set (which uses character-length semantics by default). To override the default you can specify
CHARACTER, as shown in the following syntax:
LENGTH parameter is placed after the
CHARACTERSET parameter in the SQL*Loader control file. The
LENGTH parameter applies to the syntax specification for primary data files and also to LOBFILEs and secondary data files (SDFs). A
LENGTH specification before the
INFILE parameters applies to the entire list of primary data files. The
LENGTH specification specified for the primary data file is used as the default for LOBFILEs and SDFs. You can override that default by specifying
LENGTH with the LOBFILE or SDF specification. Unlike the
CHARACTERSET parameter, the
LENGTH parameter can also apply to data contained within the control file itself (that is,
INFILE * syntax).
You can specify
CHARACTER instead of
CHAR for the
If character-length semantics are being used for a SQL*Loader data file, then the following SQL*Loader data types will use character-length semantics:
EXTERNAL numerics (
VARCHAR data type, the length subfield is still a binary
SMALLINT length subfield, but its value indicates the length of the character string in characters.
The following data types use byte-length semantics even if character-length semantics are being used for the data file, because the data is binary, or is in a special binary-encoded form in the case of
The start and end arguments to the
POSITION parameter are interpreted in bytes, even if character-length semantics are in use in a data file. This is necessary to handle data files that have a mix of data of different data types, some of which use character-length semantics, and some of which use byte-length semantics. It is also needed to handle position with the
VARCHAR data type, which has a
SMALLINT length field and then the character data. The
SMALLINT length field takes up a certain number of bytes depending on the system (usually 2 bytes), but its value indicates the length of the character string in characters.
Character-length semantics in the data file can be used independent of whether character-length semantics are used for the database columns. Therefore, the data file and the database columns can use either the same or different length semantics.