Oracle SQL*Loader: The Definitive Guide

Chapter 2. The Mysterious Control File

The control file is the key to SQL*Loader. Understanding the control file is like having the keys to the kingdom. You’ll not only be able to get work done, you’ll be able to leverage all of SQL*Loader’s built-in capabilities. As a result, you’ll work less, and SQL*Loader will work more.

This chapter describes the three parts of the control file:

• The LOAD statement

• Command-line parameters (the OPTIONS command)

• Data

The LOAD statement is present in any SQL*Loader control file. Command-line parameters and data are optional.

Syntax Rules

Before getting into the details of the LOAD, it’s worth taking the time to understand some things about control file syntax. There are various issues with respect to formatting, case sensitivity, special characters, and reserved words that you should at least be aware of. Usually you don’t need to think much about any of these issues, but sooner or later you will find all this information to be helpful.

Free Format

Control file syntax is free format in the sense that you can insert any amount of whitespace between any two syntax elements. A syntax element could be, for instance, a keyword, or it could be a value that you supply. Carriage returns, line feeds, and tabs, as well as space characters, are all considered whitespace. Thus, the following two LOAD statements are considered identical even though they are formatted differently:

LOAD DATA INFILE 'mi_deci.' BADFILE 'mn_deci.bad' DISCARDFILE 'mn_deci.dis' TRUNCATE ...