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 CHAR
or CHARACTER
, as shown in the following syntax:
The 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 LENGTH
parameter.
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:
CHAR
VARCHAR
VARCHARC
DATE
EXTERNAL
numerics (INTEGER
, FLOAT
, DECIMAL
, and ZONED
)
For the 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 ZONED
and DECIMAL
:
INTEGER
SMALLINT
FLOAT
DOUBLE
BYTEINT
ZONED
DECIMAL
RAW
VARRAW
VARRAWC
GRAPHIC
GRAPHIC EXTERNAL
VARGRAPHIC
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.