Syntax for Termination and Enclosure Specification

The following diagram shows the syntax for termination_spec and enclosure_spec.

Description of the illustration terminat.eps follows
Description of the illustration terminat.eps
Description of the illustration enclose.eps follows
Description of the illustration enclose.eps

Table 10-3 describes the syntax for the termination and enclosure specifications used to specify delimiters.

Table 10-3 Parameters Used for Specifying Delimiters

Parameter Description

TERMINATED

Data is read until the first occurrence of a delimiter.

BY

An optional word to increase readability.

WHITESPACE

Delimiter is any whitespace character including spaces, tabs, blanks, line feeds, form feeds, or carriage returns. (Only used with TERMINATED, not with ENCLOSED.)

OPTIONALLY

Data can be enclosed by the specified character. If SQL*Loader finds a first occurrence of the character, then it reads the data value until it finds the second occurrence. If the data is not enclosed, then the data is read as a terminated field. If you specify an optional enclosure, then you must specify a TERMINATED BY clause (either locally in the field definition or globally in the FIELDS clause).

ENCLOSED

The data will be found between two delimiters.

string

The delimiter is a string.

X'hexstr'

The delimiter is a string that has the value specified by X'hexstr' in the character encoding scheme, such as X'1F' (equivalent to 31 decimal). "X" can be either lowercase or uppercase.

AND

Specifies a trailing enclosure delimiter that may be different from the initial enclosure delimiter. If AND is not present, then the initial and trailing delimiters are assumed to be the same.

EOF

Indicates that the entire file has been loaded into the LOB. This is valid only when data is loaded from a LOB file. Fields terminated by EOF cannot be enclosed.

Here are some examples, with samples of the data they describe:

TERMINATED BY ','                      a data string, 
ENCLOSED BY '"'                        "a data string" 
TERMINATED BY ',' ENCLOSED BY '"'      "a data string", 
ENCLOSED BY '(' AND ')'                (a data string)