SQL*Loader - Loading Data from Data Files

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 is useful when you need to load the files in batch mode. SQL* Loader supports three different type of data files. You will need to specify the “INFILE” parameter with the file format and additional parameters required.

Fixed Record Format: This format is useful when you have a data file with fixed layout.

Variable Record Format: This format is used when you have different record lengths in the data file. You will need to specify the record length in the beginning of the each record. This format provided greater flexibility to have the data loaded compared to fixed record formatted files.

Stream Record Format: This format is used when the records are not in the fixed or specified size. Each record can be any length and records will be identified by using the record terminator.

You can specify terminator_string either in character or hexadecimal format. Char is enclosed in single or double quotes and hexadecimal should be used when nonprintable characters like new line feed or tab characters.

There are few types of hex characters which can be used. Please note that these may change based on the operating system you are using. In Unix/Linux based systems, the default to the line feed character will be n and Windows uses either n or rn as the default record terminator. Just to avoid any issues with various character sets, you may want to check NLS_LANG parameters for your session. Check to make sure that your record terminator is not part of the data record.

There are various options available, please use “sqlldr” for the parameters and usage.

Now let us take a look at the loading some sample data to a table using command prompt and OEM interface.