The DELIMITED BY clause is used to indicate the characters that identify the end of a record.
If DELIMITED BY NEWLINE is specified, then the actual value used is platform-specific. On UNIX platforms, NEWLINE is assumed to be "\n". On Windows operating systems, NEWLINE is assumed to be "\r\n".
If DELIMITED BY string is specified, then string can be either text or a series of hexadecimal digits enclosed within quotation marks and prefixed by OX or X. If it is text, then the text is converted to the character set of the data file and the result is used for identifying record boundaries. See "string".
If the following conditions are true, then you must use hexadecimal digits to identify the delimiter:
The character set of the access parameters is different from the character set of the data file.
Some characters in the delimiter string cannot be translated into the character set of the data file.
The hexadecimal digits are converted into bytes, and there is no character set translation performed on the hexadecimal string.
If the end of the file is found before the record terminator, then the access driver proceeds as if a terminator was found, and all unprocessed data up to the end of the file is considered part of the record.
Do not include any binary data, including binary counts for VARCHAR and VARRAW, in a record that has delimiters. Doing so could cause errors or corruption, because the binary data will be interpreted as characters during the search for the delimiter.
The following is an example of using DELIMITED BY records.
CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (RECORDS DELIMITED BY '|' FIELDS TERMINATED BY ','
(first_name CHAR(7),
last_name CHAR(8),
year_of_birth CHAR(4)))
LOCATION ('info.dat'));
Alvin,Tolliver,1976|Kenneth,Baer,1963|Mary,Dube,1973