0363-0368

Previous Table of Contents Next

Page 363

CHAPTER 16

SQL*Loader

IN THIS CHAPTER

• Basic SQL*Loader Components 364
• Physical Versus Logical Records 368
• Concatenated Records 369
• SQL*Loader Paths 369
• Parallel Data Loading 372
• Control File Syntax 374
• Command-Line Options and Parameter Files 387

Page 364

One of the many challenges DBAs face today is migrating data from external sources into the Oracle database. This task has increased in complexity with the introduction of data warehousing; the demand has gone from migrating megabytes of data to gigabytes, and in some cases, even terabytes. Oracle addresses this need with the SQL*Loader utility, a versatile tool that loads external data into Oracle database tables. SQL*Loader is flexible, and it is configurable to the point that you often can avoid developing 3GL procedures with embedded SQL. Whenever you face the task of converting foreign data into the Oracle format, first consider the use of SQL*Loader before resorting to other alternatives.

The purpose of this chapter is to provide an overview of SQL*Loader's functionality, highlight its capabilities, describe commonly used syntax, and provide practical examples. For additional information and in-depth reference material, refer to the Oracle8 Server Utilities Guide.

NOTE

The Oracle8 SQL*Loader was enhanced to support the loading of partitioned objects in the database. A partitioned object in Oracle8 is a table or index consisting of partitions that are grouped, typically by common logical attributes. Each partition is stored in a separate segment of the database and can have different physical attributes.

Oracle8 SQL*Loader partitioned object support enables the SQL*Loader to load the following:

• A single partition of a partitioned table
• All partitions of a partitioned table
• A non-partitioned table

Basic SQL*Loader Components

SQL*Loader requires two types of input: the external data, which can reside on disk or tape, and control information (contained in the control file), which describes the characteristics of the input data and the tables and columns to load. The outputs, some of which are optional, include the Oracle tables, log file, bad files, and discard files. Figure 16.1 illustrates the components.

The Input Data

SQL*Loader can process practically any type of data file, and it supports native datatypes for almost any platform. Data is usually read from one or more data files; however, data can also be embedded in the control file after the control information. The data file can exist as a fixed or variable format.

In fixed format, the data exists in fixed-length records that all have the same format. The fields for fixed-format files are defined by starting and ending positions within the record, and the fields contain the same datatype and length throughout the file. (See Figure 16.2.) Binary data must be in a fixed-format file because SQL*Loader cannot handle it in a variable format.

Page 365

Figure 16.1.
SQL*Loader
components.



Figure 16.2.
Fixed-format records.

In variable-format files, the data exists in records that may vary in length, depending on the length of the data in the fields. The fields are only as long as necessary to contain the data. Fields in variable-format files can be separated by termination characters (such as commas or white space), enclosed by delimiter characters (such as quotation marks), or both. (See Fig- ure 16.3.)

Figure 16.3.
Variable-format
records.

Page 366

NOTE

If you are using files with termination characters, make sure that any field that contains the termination character as part of the data is delimited. If, for example, you are using a file with comma separation, you can use double quotes to delimit any field containing a comma.

TIP

To increase performance, use fixed-length records. Based on the tests I have done, SQL*Loader takes about 50 percent longer to process a variable-format file than a fixed-format file. I used the same amount of data for both formats and comma separation for the variable data.

Through Oracle National Language Support (NLS), SQL*Loader has the capability to interpret and convert data with different character encoding schemes from other computer platforms and countries . SQL*Loader, for example, can load an EBCDIC file into a database on an ASCII platform, or it can load an Asian character-set file into an American character-set database.

NOTE

When porting a file from one platform (such as an EBCDIC mainframe) to a different target platform (such as ASCII UNIX) that contains binary data (such as packed decimal), remember that the file is transferred in an image or binary state. If, for example, you are using FTP to port the file, after you connect to the host system, be certain to put the session in an image state by typing binary at the FTP prompt. Otherwise, FTP interprets and converts the data, thereby corrupting the binary data.

The Control File

Before SQL*Loader can process the data in the data files, you must define the data specifications to SQL*Loader. You use the control file to define the physical data file specifications, as well as the format of the data in the files. The control file is a free-format file that also contains additional control data, instructing SQL*Loader how to process the data. The details concerning the control file are described in a later section.

The Log File

Upon execution, SQL*Loader creates a log file containing detailed information about the load, including these items:

Page 367

• Names of the input data files, control file, bad files, and discard files
• Input data and associated table specifications
• SQL*Loader errors
• SQL*Loader results
• Summary statistics

Figure 16.4 shows a sample log file.

Figure 16.4.
Sample log file.

Page 368

Discard and Bad Files

SQL*Loader has the built-in functionality, through specifications in the control file, to format the input data and include or exclude the input record based on record-selection criteria. If SQL*Loader includes the record for processing, it is passed to the Oracle kernel for insertion into the database tables. Figure 16.5 shows the record-filtering process.

Figure 16.5.
Record-filtering process.

If SQL*Loader rejects the record due to a format error, or the Oracle kernel cannot insert the record into the database tables for any reason, the input record is written to the BAD file in the same format as the original input data file. If SQL*Loader rejects the record due to an exclusion based on the record-selection criteria, the input record is written to the DISCARD file (providing it was specified in the control file), also in the same format as the original input data file. Because the BAD and DISCARD files are written in the same format as the original input data file, they can be edited, if necessary, and used as input data files to another SQL*Loader session.

Physical Versus Logical Records

Physical records are the individual lines in the input data file as they were created by the operating system on the platform from which the file originated. Physical records are terminated by a record-terminator character (such as a carriage return). Logical records correspond to a row in a database table. A physical record can have a one-to-one association with a logical record. (See Figure 16.6.) SQL*Loader also enables you to create a many-to-one association between physical and logical records through the use of the CONCATENATE or CONTINUEIF clause in the control file. You can create a one-to-many relationship by splitting one physical record into multiple logical records.

Previous Table of Contents Next