Stream Record Format

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"]

The str indicates the file is in stream record format. The terminator_string is specified as either 'char_string' or X'hex_string' where:

  • 'char_string' is a string of characters enclosed in single or double quotation marks

  • X'hex_string' is a byte string in hexadecimal format

When the terminator_string contains special (nonprintable) characters, it should be specified as an X'hex_string'. However, some nonprintable characters can be specified as ('char_string') 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, \n.

On Windows-based platforms, if no terminator_string is specified, then SQL*Loader uses either \n or \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.

See Also:

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,|