Oracle8 Utilities Release 8.0 A58244-01 |
Library | Product | Contents | Index

# 3
SQL*Loader Concepts

This chapter explains the basic concepts of loading data into an Oracle database with SQL*Loader. This chapter covers the following topics:

- SQL*Loader Basics
- SQL*Loader Control File
- Input Data and Datafiles
- Data Conversion and Datatype Specification
- Discarded and Rejected Records
- Log File and Logging Information
- Conventional Path Load versus Direct Path Load
- Partitioned Object Support

Note: If you are using Trusted Oracle, see the Trusted Oracle documentation for information about using the SQL*Loader in that environment.

## SQL*Loader Basics

SQL*Loader loads data from external files into tables in an Oracle database. SQL*Loader has many features of the DB2 Load Utility from IBM, as well as several other features that give it additional power and flexibility. SQL*Loader accepts input data in a variety of formats, can perform filtering (selectively loading records based upon their data values), and can load data into multiple Oracle database tables during the same load session.

Figure 3-1 shows the basic components of a SQL*Loader session in operation.

#### Figure 3-1 SQL*Loader Overview

SQL*Loader takes a control file as its input, which describes the load to SQL*Loader. The control file also specifies the input datafile(s).

As it executes, SQL*Loader produces a log file where it writes information about the load. If records are rejected (typically because of incorrect data), it produces a bad file containing the rejected records. It also may produce a discard file containing records that did not meet the specified selection criteria.

SQL*Loader can:

- Load data from multiple input datafiles of different file types
- Handle fixed-format, delimited-format, and variable-length records
- Manipulate data fields with SQL functions before inserting the data into database columns
- Support a wide range of datatypes, including DATE, BINARY, PACKED DECIMAL, and ZONED DECIMAL
- Load multiple tables during the same run, loading selected rows into each table
- Combine multiple physical records into a single logical record
- Handle a single physical record as multiple logical records
- Generate unique, sequential key values in specified columns
- Use the operating system's file or record management system to access datafiles
- Load data from disk, tape, or named pipes
- Thoroughly report errors so you can easily adjust and load all records
- Use high-performance "direct" loads to load data directly into database files without Oracle processing (discussed in Chapter 8, "SQL*Loader: Conventional and Direct Path Loads")

## SQL*Loader Control File

The control file, written in SQL*Loader data definition language (DDL), specifies how to interpret the data, what tables and columns to insert the data into, and may also include input datafile management information.

The data for SQL*Loader to load into an Oracle database must be in files accessible to SQL*Loader (typically a file in a file system, on tape, or a named pipe, depending on the platform). SQL*Loader requires information about the data to be loaded which provides instructions for mapping the input data to columns of a table. These instructions are written in SQL*Loader DDL, typically by the DBA using the system text editor. Following are some of the items that are specified in the SQL*Loader control file:

- Specifications for loading logical records into tables
- Field condition specifications
- Column and field specifications
- Data-field position specifications
- Datatype specifications
- Bind array size specifications
- Specifications for setting columns to null or zero
- Specifications for loading all-blank fields
- Specifications for trimming blanks and tabs
- Specifications to preserve white space
- Specifications for applying SQL operators to fields

SQL*Loader DDL is upwardly compatible with the DB2 Load Utility from IBM. Normally you can use a control file for the DB2 Load Utility as a control file for SQL*Loader. See Appendix B, "DB2/DXT User Notes" for differences in syntax.

### Control File Contents and Storage

Some DDL statements are mandatory. They must define where to find the input data. They must also define the correspondence between the input data and the Oracle database tables or indexes.

DDL options are available to describe and manipulate the file data. For example, the instructions can include how to format or filter the data, or how to generate unique ID numbers for a field.

A control file can contain the data itself after the DDL statements, as shown in Case 1, or in separate files, as shown in Case 2 on . Detailed information on creating control files using SQL*Loader DDL is given in "Data Definition Language (DDL) Syntax" on page 5-4.

#### Content Guidelines

- The control file is written in free format. That is, statements can continue from line to line with new lines beginning at any word.
- Uppercase or lowercase is not significant except in strings specified within single or double quotation marks.
- Comments can be included, prefixed by two hyphens (--). A comment can appear anywhere on a line. SQL*Loader ignores everything from the double hyphens to the end of line.
- SQL*Loader does not recognize comments in a datafile or in the data portion of the control file. It considers a double dash in those areas to be data.
- SQL*Loader reserved words (see Appendix A, "SQL*Loader Reserved Words" for a complete list) can serve for database object's names if they are enclosed in single or double quotation marks.

#### Storage

How you store the control file depends on how your operating system organizes data. For example, a UNIX environment stores a control file in a file; in MVS environments, the control file can be stored as a member in a partitioned dataset.

The control file must be stored where SQL*Loader can read it.

### Data Definition Language (DDL)

SQL*Loader data definition language (DDL) is used to specify how SQL*Loader should map the input data it is loading to the columns of a table in an Oracle database. Chapter 5, "SQL*Loader Control File Reference" details the syntax and semantics of SQL*Loader DDL.

DDL statements serve several purposes. Some statements specify input data location or format. Other DDL statements specify which Oracle table to load, mapping of the columns of a table to fields within an input record (field specifications), and specification of the loader input datatype of a field.

A single DDL statement comprises one or more keywords and the arguments and options that modify that keyword's functionality. The following example from a control file contains several statements specifying how SQL*Loader is to load the data from an input datafile into a table in an Oracle database:

    LOAD DATA
    INFILE 'example.dat'
    INTO TABLE emp
    (empno         POSITION(01:04)  INTEGER EXTERNAL,
      ename          POSITION(06:15)  CHAR,
      job            POSITION(17:25)  CHAR,
      mgr            POSITION(27:30)  INTEGER EXTERNAL,
      sal            POSITION(32:39)  DECIMAL EXTERNAL,
      comm           POSITION(41:48)  DECIMAL EXTERNAL,
    ...

This example shows the keywords LOAD DATA, INFILE, INTO TABLE, and POSITION.

## Input Data and Datafiles

The data for SQL*Loader to load into an Oracle database must be accessible to SQL*Loader, typically in files on disk or tape, or via a named pipe.

### Input Data Formats

SQL*Loader can load data (see "Binary versus Character Data" on page 3-9) that has been stored in data fields (see "Data Fields") and records of various formats. The record format may be specified in the control file as a file processing option. SQL*Loader recognizes the following three record formats:

- Stream Record Format (Default Format)
- Fixed Record Format
- Variable Record Format

#### Stream Record Format (Default Format)

Stream format records are only as long as needed to contain the data. Each physical record ends with a terminating character (or characters on some platforms). The record terminator is typically a newline character (`\n'), or a carriage return followed by a newline ("\r\n"). The record terminator used is platform dependent. Case 3 shows delimited fields.

#### Fixed Record Format

In fixed format, the data records all have the same fixed-length format. That is, every record is the same fixed length, and the data fields in each record have the same fixed length, type, and position, as shown in Figure 3-2.

#### Figure 3-2 Fixed Format Records

In this figure, each record's bytes 1 through 6 are specified to contain CHAR data while bytes 7 through 10 are specified to contain INTEGER data. The fields are the same size in each record, regardless of the length of the data; the fields are fixed length rather than variable length. The record size also is fixed, at 10 bytes for each record. Case 2 shows fixed-length records.

Fixed format records make each record exactly the same number of bytes long and each specified field within each record of the specified data type and specified length. The processing option specification in the control file should contain the string "fix n", where n is the size of the record. Note that if you are loading data that is contained in the control file itself (using INFILE *), you cannot load using fixed length.

The following example control file and data will load the included records, which have a fixed length of 11 characters (10 characters of data plus one newline character for ease of editing the data with the system text editor). It is important to note that SQL*Loader does not require a record terminator for fixed length records, but if a record terminator is present, it must be included in the record length.

##### Fixed Format Example Control File and Data

    load data
    infile `example.dat'  "fix 11"
    badfile `example.bad'
    discardfile `example.dsc'
    discardmax 999
    truncate
    into table example
    (rown position(1-5), cmnt position(6-10))
    example.dat:
    00001abcde
    00002fghij
    00003klmno
    00004pqrst
    00005uvwxy

#### Variable Record Format

Figure 3-3 shows variable length records containing one varying-length character field and one varying-length integer field. In this format, the length of each field and record can vary. For example, in each record the first field may be specified to hold a character string, separated by a space from the next field, which may be specified to hold an integer.

If the operating system record-terminator character (such as newline) is used to mark where varying-length records end for ease of editing with a text editor, the record size must include the record-terminator.

#### Figure 3-3 Variable Format Records

##### Variable Format Example Control File and Data

This example uses variable record formats where each record has a variable length n, and n is specified in the first 5 characters of the record. The file processing option should contain the string "var". The following control and data file will load records with variable length records:

    load data
    infile `example.dat'  "var"
    badfile `example.bad'
    discardfile `example.dsc'
    discardmax 999
    truncate
    into table example
    fields terminated by "," optionally enclosed by `"'
    (rown, cmnt, len)
    example.dat:
    0001500001,a,00015,
    0005000002,abcdefghijklmnopqrstuvwxyzABCDEFGHIJ,00050,
    0005900003,abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRS,00059,
    0005200004,abcdefghijklmnopqrstuvwxyzABCDEFGHIJKL,00052,
    0004800005,abcdefghijklmnopqrstuvwxyzABCDEFGH,00048,

Note: For fixed and variable length record formats no assumptions are made about newlines (or other terminator(s)) at the end of each physical record.

#### Logical versus Physical Records

Another distinction is the difference between logical and physical records. A record or line in a file (either of fixed length or terminated) is considered a physical record. An operating system-dependent file/record management system, such as DEC's Record Management System (RMS) or IBM's Sequential Access Method (SAM) returns physical records.

A logical record, however, comprises one or more physical records. Sometimes the logical and physical records are equivalent. Such is the case when only a few short columns are being loaded. However, sometimes several physical records must be combined to make one logical record. For example, a single logical record containing twenty-four 10-character columns could comprise three 80-character files; thus three physical records would constitute the single logical record.

SQL*Loader allows you to compose logical records from multiple physical records by using continuation fields. Physical records are combined into a single logical record when some condition of the continuation field is true. You can specify that a logical record be composed of multiple, physical records in the following ways:

- A fixed number of physical records are concatenated to form a logical record (no continuation field is used).
- Physical records are appended if the continuation field contains a specified string (or another test, such as "not equal", succeeds when applied to the continuation field).
- Physical records are appended if they contain a specified character as their last non-blank character.

#### Binary versus Character Data

Binary is a "native" datatype. Native datatypes may be implemented differently on different operating systems or on different hardware architectures. For more information on native datatypes, see "Native Datatypes" on page 5-51.

Character data can be included in any record format. For more information on character datatypes, see "Character Datatypes" on page -58.

SQL*Loader can load numeric data in binary or character format. Character format is sometimes referred to as numeric external format.

Binary data should not be loaded using stream record format (newline terminated records) as a binary input field may contain a newline character that would incorrectly be considered to be the record delimiter.

#### Data Fields

Data (binary or character) in records is broken up into fields. Each field can be specified in terms of a specific position and length, or fields' positions and lengths can vary, limited by delimiters, in the following example, commas:

    1,1,2,3,5,8,13

Enclosed fields are both preceded and followed by enclosure delimiters, such as the quotation marks in the following example:

    "BUNKY"

## Data Conversion and Datatype Specification

Figure 3-4 shows the stages in which fields in the datafile are converted into columns in the database during a conventional path load (direct path loads are conceptually similar, but the implementation is different.) The top of the diagram shows a data record containing one or more fields. The bottom shows the destination database column. It is important to understand the intervening steps when using SQL*Loader.

Figure 3-4 depicts the "division of labor" between SQL*Loader and the Oracle8 server. The field specifications tell SQL*Loader how to interpret the format of the datafile. The Oracle8 server then converts that data and inserts it into the database columns, using the column datatypes as a guide.

Keep in mind the distinction between a field in a datafile and a column in the database. Remember also that the field datatypes defined in a SQL*Loader control file are not the same as the column datatypes.

SQL*Loader uses the field specifications in the control file to parse the input data and populate the bind arrays which correspond to a SQL insert statement using that data. The insert statement is then executed by the Oracle8 server to be stored in the table. The Oracle8 server uses the datatype of the column to convert the data into its final, stored form.

In actuality, there are two conversion steps:

1. SQL*Loader identifies a field in the datafile, interprets the data, and passes it to the Oracle8 server via a bind buffer.
2. The Oracle8 server accepts the data and stores it in the database.

   #### Figure 3-4 Translation of Input Data Field to Oracle Database Column

In Figure 3-5, two CHAR fields are defined for a data record. The field specifications are contained in the control file. Note that the control file CHAR specification is not the same as the database CHAR specification. A data field defined as CHAR in the control file merely tells SQL*Loader how to create the row insert. The data could then be inserted into a CHAR, VARCHAR2, NCHAR, NVARCHAR, or even a NUMBER column in the database, with the Oracle8 Server handling any necessary conversions.

By default, SQL*Loader removes trailing spaces from CHAR data before passing it to the database. So, in Figure 3-5, both field A and field B are passed to the database as three-column fields. When the data is inserted into the table, however, there is a difference.

#### Figure 3-5 Example of Field Conversion

Column A is defined in the database as a fixed-length CHAR column of length 5. So the data (aaa) is left justified in that column, which remains five characters wide. The extra space on the right is padded with blanks. Column B, however, is defined as a varying length field with a maximum length of five characters. The data for that column (bbb) is left-justified as well, but the length remains three characters.

The name of the field tells SQL*Loader what column to insert the data into. Because the first data field has been specified with the name "A" in the control file, SQL*Loader knows to insert the data into column A of the target database table.

It is useful to keep the following points in mind:

- The name of the data field corresponds to the name of the table column into which the data is to be loaded.
- The datatype of the field tells SQL*Loader how to treat the data in the datafile (e.g. bind type). It is not the same as the column datatype. SQL*Loader input datatypes are independent of the column datatype.
- Data is converted from the datatype specified in the control file to the datatype of the column in the database.
- The distinction between logical records and physical records.

## Discarded and Rejected Records

Records read from the input file might not be inserted into the database. Figure 3-6 shows the stages at which records may be rejected or discarded.

### The Bad File

The bad file contains records rejected, either by SQL*Loader or by Oracle. Some of the possible reasons for rejection are discussed in the next sections.

#### SQL*Loader Rejects

Records are rejected by SQL*Loader when the input format is invalid. For example, if the second enclosure delimiter is missing, or if a delimited field exceeds its maximum length, SQL*Loader rejects the record. Rejected records are placed in the bad file. For details on how to specify the bad file, see "Specifying the Bad File" on page 5-19

#### Figure 3-6 Record Filtering

#### Oracle Rejects

After a record is accepted for processing by SQL*Loader, a row is sent to Oracle8 for insertion. If Oracle determines that the row is valid, then the row is inserted into the database. If not, the record is rejected, and SQL*Loader puts it in the bad file. The row may be rejected, for example, because a key is not unique, because a required field is null, or because the field contains invalid data for the Oracle datatype.

The bad file is written in the same format as the datafile. So rejected data can be loaded with the existing control file after necessary corrections are made.

Case 4 is an example of the use of a bad file.

### SQL*Loader Discards

As SQL*Loader executes, it may create a file called the discard file. This file is created only when it is needed, and only if you have specified that a discard file should be enabled (see "Specifying the Discard File" on page 5-21). The discard file contains records that were filtered out of the load because they did not match any record-selection criteria specified in the control file.

The discard file therefore contains records that were not inserted into any table in the database. You can specify the maximum number of such records that the discard file can accept. Data written to any database table is not written to the discard file.

The discard file is written in the same format as the datafile. The discard data can be loaded with the existing control file, after any necessary editing or correcting.

Case 4 shows how the discard file is used. For more details, see "Specifying the Discard File" on page 5-21.

## Log File and Logging Information

When SQL*Loader begins execution, it creates a log file. If it cannot create a log file, execution terminates. The log file contains a detailed summary of the load, including a description of any errors that occurred during the load. For details on the information contained in the log file, see Chapter 7, "SQL*Loader: Log File Reference". All of the case studies in Chapter 4 also contain sample log files.

## Conventional Path Load versus Direct Path Load

SQL*Loader provides two methods to load data: Conventional Path, which uses a a SQL INSERT statement with a bind array, and Direct Path, which loads data directly into a database. These modes are discussed below and, more thoroughly, in Chapter 8, "SQL*Loader: Conventional and Direct Path Loads". The tables to be loaded must already exist in the database, SQL*Loader never creates tables, it loads existing tables. Tables may already contain data, or they may be empty.

The following privileges are required for a load:

- You must have INSERT privileges on the table to be loaded.
- You must have DELETE privilege on the table to be loaded, when using the REPLACE or TRUNCATE option to empty out the table's old data before loading the new data in its place.

In addition to the above privileges, you must have write access to all labels you are loading data into a Trusted Oracle database. See the Trusted Oracle7 Administrator's Guide.

#### Conventional Path

During conventional path loads, the input records are parsed according to the field specifications, and each data field is copied to its corresponding bind array. When the bind array is full (or there is no more data left to read), an array insert is executed. For more information on conventional path loads, see "Data Loading Methods" on page 8-2. For information on the bind array, see "Determining the Size of the Bind Array" on page 5-65.

There are no special requirements for tables being loaded via the conventional path.

#### Direct Path

A direct path load parses the input records according to the field specifications, converts the input field data to the column datatype and builds a column array. The column array is passed to a block formatter which creates data blocks in Oracle database block format. The newly formatted database blocks are written directly to the database bypassing most RDBMS processing. Direct path load is much faster than conventional path load, but entails several restrictions. For more information on the direct path, see "Data Loading Methods" on page 8-2.

#### Parallel Direct Path

A parallel direct path load allows multiple direct path load sessions to concurrently load the same data segments (allows intra-segment parallelism). Parallel Direct Path is more restrictive than Direct Path. For more information on the parallel direct path, see "Data Loading Methods" on page 8-2.

## Partitioned Object Support

The Oracle8 SQL*Loader supports loading partitioned objects in the database. A partitioned object in Oracle8 is a table or index consisting of partitions (pieces) that have been grouped, typically by common logical attributes. For example, sales data for the year 1997 might be partitioned by month. The data for each month is stored in a separate partition of the sales table. Each partition is stored in a separate segment of the database and can have different physical attributes.

Oracle8 SQL*Loader Partitioned Object Support enables SQL*Loader to load the following:

- A single partition of a partitioned table
- All partitions of a partitioned table
- Non-partitioned table

Oracle8 SQL*Loader supports partitioned objects in all three paths (modes):

- Conventional Path: changed minimally from Oracle7, as mapping a row to a partition is handled transparently by SQL.
- Direct Path: changed significantly from Oracle7 to accommodate mapping rows to partitions of tables, to support local indexes, and to support global indexes, which can also be partitioned; direct path bypasses SQL and loads blocks directly into the database.
- Parallel Direct Path: changed from Oracle7 to include support for concurrent loading of an individual partition and also a partitioned table; allows multiple direct path load sessions to load the same segment or set of segments concurrently. Parallel direct path loads are used for intra-segment parallelism. Note that inter-segment parallelism can be achieved by concurrent single partition direct path loads with each load session loading a different partition of the same table.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index