Oracle in a Nutshell

Chapter 14. SQL*Loader

Chapter 13 described how you can use the Export and Import utilities to move data from an Oracle database and into an Oracle database. The file used for this transfer is a binary file. SQL*Loader, described in this chapter, is another Oracle utility that is designed to load data in standard operating system file formats into an Oracle database.

SQL*Loader offers many options for manipulating and transforming the data that it loads. For example, you can:

• Read from multiple files and load into multiple database tables; in addition to standard relational tables, these tables may be object tables, nested tables, varying arrays (VARRAYs), and large object (LOB) columns

• Handle files with fixed-length, variable-length, and stream-oriented data of many different datatypes

• Manipulate the data being loaded by dealing with null data and various delimiters and performing character set translation

Five basic files may be involved in a SQL*Loader job:

Input data file(s)

Operating system files containing the actual data; these may be fixed-length or delimited files.

Control file

Text file that contains all the information SQL*Loader needs to load the data from the data file.

Log file

Logs the results of SQL*Loader operations.

Bad file

Created if errors result from bad data in the input file(s). This file holds the records containing bad data (e.g., data with datatype mismatches, constraint violations, etc.). If the SQL*Loader run completes successfully, no bad file is created. ...