Loading Data with SQL*Loader

SQL*Loader loads data from external datafiles into tables of an Oracle database. A particular datafile can be in fixed record format, variable record format, or stream record format (the default).

The input for a typical SQL*Loader session is a contr ol file, which controls the behavior of SQL*Loader, and some data, located either at the end of the control file itself, or in a separate datafile.

The output of a SQL*Loader session is an Oracle database (where the data is loaded), a log file, a "bad" file, and potentially, a discard file. The log file contains a detailed summary of the load, including a description of any errors that occurred during the load. The bad file contains records that were rejected, either by SQL*Loader or by the Oracle database. The discard file contains records that were filtered out of the load because they did not match any reco rd-selection criteria specified in the control file.

Methods SQL*Loader Uses to Load Data

SQL*Loader uses three different methods to load data, depending on the situation: conventional path, direct path, and external tables.

Conventional Path

A conventional path load is the default loading method. It executes SQL INSERT statements to populate tables in an Oracle database. This method can sometimes be slower than other methods because extra overhead is added as SQL statements are generated, passed to Oracle, and executed. It can also be slower because when SQL*Loader performs a conventional path load, it competes equally with all other processes for buffer resources.

Direct Path

A direct path load does not compete with other users for database resources. It eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing them directly to the database files, bypassing much of the data processing that normally takes place. Therefore, a direct path load can usually load data faster than conventional path. However, there are several restrictions on direct path loads that may require you to use a conventional path load. For example, direct path load cannot be used on clustered tables or on tables for which there are transactions pending.

See Oracle Database Utilities for a complete discussion of situations in which direct path load should and should not be used.

External Tables

An external table load creates an external table for data that is contained in a datafile. The load executes INSERT statements to insert the data from the datafile into the target table. An external table load allows modification of the data being loaded by using SQL functions and PL/SQL functions as part of the INSERT statement that is used to create the external table.

See Oracle Database Administrator's Guide for more information on external tables.

SQL*Loader Features

You can use SQL*Loader to do the following:

Load data across a network. This means that you can run the SQL*Loader client on a different system from the one that is running the SQL*Loader server.
Load data from multiple data files during the same load session.
Load data into multiple tables during the same load session.
Specify the character set of the data.
Selectively load data (you can load records based on the records' values).
Manipulate the data before loading it, using SQL functions.
Generate unique sequential key values in specified columns.
Use the operating system's file system to access the datafiles.
Load data from disk, tape, or named pipe.
Generate sophisticated error reports, which greatly aid troubleshooting.
Load arbitrarily complex object-relational data.
Use secondary datafiles for loading LOBs and collections.

Example: Using SQL*Loader

In the following example, a new table named dependents will be created in the HR sample schema. It will contain information about dependents of employees listed in the employees table of the HR schema. After the table is created, SQL*Loader will be used to load data about the dependents from a flat data file into the dependents table.

This example requires a data file and a SQL*Loader control file, which you will create in the first two steps.

Create the data file, dependents.dat, in your current working directory. You can create this file using a variety of methods, such as a spreadsheet application or by simply typing it into a text editor. It should have the following content:
```
100,"Susan, Susie",Kochhar,17-JUN-1997,daughter,101,NULL,
102,David,Kochhar,02-APR-1999,son,101,NULL,
104,Jill,Colmenares,10-FEB-1992,daughter,119,NULL,
106,"Victoria, Vicki",Chen,17-JUN-1997,daughter,110,NULL,
108,"Donald, Donnie",Weiss,24-OCT-1989,son,120,NULL,
```
This file is a CSV (comma-separated values) file in which the commas act as delimiters between the fields. The field containing the first name is enclosed in double quotation marks in cases where a variant of the official name is also provided—that is, where the first name field contains a comma.

Create the SQL*Loader control file, dependents.ctl, in your current working directory. You can create this file with any text editor. It should have the following content:

LOAD DATA
INFILE dependents.dat
INTO TABLE dependents
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
dep_id,
first_name,
last_name,
birthdate,
relation,
relative_id,
benefits
)

Do one of the following:
- On Linux: Start a terminal session and log in to the Oracle Database XE host computer with the oracle user account.
- On Windows: Log in to the Oracle Database XE host computer as the user who installed Oracle Database XE, and then open a command window.
On Linux, ensure that environment variables are set according to the instructions in "Setting Environment Variables on the Linux Platform".
Start SQL Command Line (SQL*Plus) and connect as user hr by entering the following at the command prompt:
```
sqlplus hr/hr
```
At the SQL prompt, create the dependents table, as follows:
```
CREATE TABLE dependents (
  dep_id      NUMBER(6),
  first_name  VARCHAR2(20),
  last_name   VARCHAR2(25) CONSTRAINT dep_last_name_nn NOT NULL,
  birthdate   DATE,
  relation    VARCHAR2(25),
  relative_id  NUMBER(6) CONSTRAINT emp_dep_rel_id_fk REFERENCES employees
    (employee_id),
  benefits    CLOB
)
/
```
The constraint on the last_name column indicates that a value must be provided. The constraint on the relative_id column indicates that it must match a value in the employee_id column of the employees table. The benefits column has a datatype of CLOB so that it can hold large blocks of character data. (In this example, there is not yet any benefits information available so the column is shown as NULL in the data file, dependents.dat.)

After you receive the Table created message, enter exit to exit SQL Command Line.

From within your current working directory (where you created the control and data files), issue the following SQL*Loader command at the system prompt:

sqlldr hr/hr DATA=dependents.dat CONTROL=dependents.ctl LOG=dependents.log

The data in the dependents.dat file is loaded into the dependents table and the following message is displayed:

Commit point reached - logical record count 5

Information about the load is written to the log file, dependents.log. The content of the log file looks similar to the following:

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
Control File:   dependents.ctl
Data File:      dependents.dat
  Bad File:     dependents.bad
  Discard File:  none specified
 
 (Allow all discards)
 
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional
 
Table DEPENDENTS, loaded from every logical record.
Insert option in effect for this table: REPLACE
 
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEP_ID                              FIRST     *   ,  O(") CHARACTER            
FIRST_NAME                           NEXT     *   ,  O(") CHARACTER            
LAST_NAME                            NEXT     *   ,  O(") CHARACTER            
BIRTHDATE                            NEXT     *   ,  O(") CHARACTER            
RELATION                             NEXT     *   ,  O(") CHARACTER            
RELATIVE_ID                          NEXT     *   ,  O(") CHARACTER            
BENEFITS                             NEXT     *   ,  O(") CHARACTER            
 
 
Table DEPENDENTS:
  5 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
 
 
Space allocated for bind array:                 115584 bytes(64 rows)
Read   buffer bytes: 1048576
 
Total logical records skipped:          0
Total logical records read:             5
Total logical records rejected:         0
Total logical records discarded:        0
 
Run began on Mon Dec 05 16:16:29 2005
Run ended on Mon Dec 05 16:16:42 2005
 
Elapsed time was:     00:00:12.22
CPU time was:         00:00:00.09

You can now work with the dependents table, as you would any other table.