A file is in stream record format when the records are not specified by size; instead SQL*Loader forms records by scanning for the record terminator. Stream record format is the most flexible format, but there can be a negative effect on performance. The specification of a data file to be interpreted as being in stream record format looks similar to the following:
INFILE datafile_name ["str terminator_string"]
str indicates the file is in stream record format. The
terminator_string is specified as either
' is a string of characters enclosed in single or double quotation marks
' is a byte string in hexadecimal format
terminator_string contains special (nonprintable) characters, it should be specified as an
'. However, some nonprintable characters can be specified as (
') by using a backslash. For example:
\n indicates a line feed
\t indicates a horizontal tab
\f indicates a form feed
\v indicates a vertical tab
\r indicates a carriage return
If the character set specified with the
NLS_LANG initialization parameter for your session is different from the character set of the data file, then character strings are converted to the character set of the data file. This is done before SQL*Loader checks for the default record terminator.
Hexadecimal strings are assumed to be in the character set of the data file, so no conversion is performed.
On UNIX-based platforms, if no
terminator_string is specified, then SQL*Loader defaults to the line feed character,
On Windows-based platforms, if no
terminator_string is specified, then SQL*Loader uses either
\r\n as the record terminator, depending on which one it finds first in the data file. This means that if you know that one or more records in your data file has
\n embedded in a field, but you want
\r\n to be used as the record terminator, then you must specify it.
Example 7-3 illustrates loading data in stream record format where the terminator string is specified using a character string,
'|\n'. The use of the backslash character allows the character string to specify the nonprintable line feed character.
Oracle Database Globalization Support Guide for information about using the Language and Character Set File Scanner (LCSSCAN) utility to determine the language and character set for unknown file text
Example 7-3 Loading Data in Stream Record Format
load data infile 'example3.dat' "str '|\n'" into table example fields terminated by ',' optionally enclosed by '"' (col1 char(5), col2 char(7)) example3.dat: 396,ty,| 4922,beth,|