Oracle 10g Free Training - SQL*Loader - Input Data and Datafiles
In this tutorial you will learn about SQL*Loader - Input Data and Datafiles, Fixed Record Format, Variable Record Format and Stream Record Format.
SQL*Loader - Input Data and Datafiles
SQL*Loader reads data from one or more files (or operating system equivalents of files) specified in the control file. From SQL*Loader's perspective, the data in the datafile is organized as records. A particular datafile can be in fixed record format, variable record format, or stream record format. The record format can be specified in the control file with the INFILE parameter. If no record format is specified, the default is stream record format.
Fixed Record Format
A file is in fixed record format when all records in a datafile are the same byte length. Although this format is the least flexible, it results in better performance than variable or stream format. Fixed format is also simple to specify. For example:
INFILE datafile_name "fix n"
Variable Record Format
A file is in variable record format when the length of each record in a character field is included at the beginning of each record in the datafile. This format provides some added flexibility over the fixed record format and a performance advantage over the stream record format. For example, you can specify a datafile that is to be interpreted as being in variable record format as follows:
INFILE "datafile_name" "var n"
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 datafile to be interpreted as being in stream record format looks similar to the following:
INFILE datafile_name ["str terminator_string"]
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 a 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 parameter for your session is different from the character set of the datafile, character strings are converted to the character set of the datafile. This is done before SQL*Loader checks for the default record terminator.
Hexadecimal strings are assumed to be in the character set of the datafile, so no conversion is performed.
On UNIX-based platforms, if no terminator_string is specified, SQL*Loader defaults to the line feed character, \n.
On Windows NT, 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 datafile. This means that if you know that one or more records in your datafile has \n embedded in a field, but you want \r\n to be used as the record terminator, you must specify it.
The screen below asks for the data file details if it is not already specified in the control file.
The below screen asks for the load method options while loading the data using SQL*Loader.
