A Guide To Oracle SQL Loader

Oracle SQL Loader is a utility for loading data into an Oracle database and is often used for transporting data from a non-Oracle source system to an Oracle data warehouse. It is a versatile utility that can load data in almost any format, can load multiple files at the same time into multiple tables and can load data from files on disk, on tape or from a named pipe.

It runs in one of 3 modes: conventional load, direct-path load and external-path load. The conventional load is the deafult method and has less restrictions (see below) than the direct-path load which is generally much faster but less flexible. The direct-path load is faster for large data sets as it doesn't generate any undo data and bypasses the database

buffer cache but it is limited to use just on heap tables (see below for the other restrictions).

The external-path load creates an external table for the specified datafile and then executes SQL INSERT statements to load the data into the target table. This mode has 2 advantages over direct-path and conventional loads:

If a data file is big enough it will be loaded in parallel;
The source data can be modified by SQL and PL/SQL functions as it is being loaded.

Oracle Tips & Tricks
to SKYROCKET Your Career!

Don't waste hours scouring the Internet for Oracle tips. Subscribe to our ezine and get them delivered straight to your in box.

Oracle SQL Loader Features

SQL*Loader enables you to:

load data from multiple files (from disk, tape or named pipe) into one or more target tables in the same load
load data in any character set supported by Oracle
load or discard records depending on values in the input fields
transform the data before loading using SQL functions
generate unique sequential keys for specified columns
append to existing data or replace existing data
load large objects (lobs), collections (nested tables and varrays) and object-relational data

How It Works

Oracle SQL Loader processes the input data files according to the directions in a text file called the control file which specifies the names and locations of the source data files, the format of the data to be loaded and the data transformations to be performed when loading.

As the input files are processed, any records that do not pass format checks are written to the bad file and any records that do not meet the specified selection criteria are written to the discard file.

Records that pass both format and selection criteria are written to the specified target tables but they may still be rejected because of, for example, constraint violations in which case they are written to the bad file along with those records rejected for being invalid.

A log file containing a detailed summary of the load, including a description of any errors that occurred during the load is also produced.

How To Use Oracle SQL Loader

Oracle SQL Loader is initiated from the command line and the various parameters such as the name of the control file and the userid can be specified at the same time but it is generally a lot easier to put all these parameters into a parameter file, thereby saving on typing and frustration when typos are made. Which of the conventional/direct path/external path load types to use is a trade off between performance and flexibility.

The default load type is the conventional load which creates and executes SQL insert statements to load the data into the target tables. This method is better when:

other users need to be able update data in the target tables whilst new data is being loaded into them;
loading data into clustered tables;
loading a relatively small amount of rows into a large indexed table as the load process makes a copy of the original index before merging in the new keys (this is a relatively slow process for a large table);
loading data into a large table with referential or column check integrity constraints as these constraints are disabled during a direct path load and re-enabled when the load finishes requiring the whole table to be checked;
you want to ensure that a record will be rejected if it causes an Oracle error, is formatted incorrectly or violates a constraint on the target table;
insert triggers must be fired.

The direct-path load is initiated by specifying DIRECT=TRUE when starting Oracle SQL Loader. This method writes formatted data blocks directly to the target tables bypassing the SQL layer making loading faster but has the restrictions mentioned above. This method should be used when you need to load a large amount of data and need to maximise performance.

When using external-path load the source file has to be in a location accessible to the database and specified by an Oracle directory and the user must have been granted read and write access to the Oracle directory.

Full details on sql loader can be found in the Oracle utilities guide

Summary

Oracle SQL Loader is a very versatile tool with many different parameters and options which can make it difficult to use. Probably the most important thing to do when using it is to make sure the format of the control file is correct (refer to the Oracle utilities guide for the exact format), start with a small sample of data to ensure it works and always check the bad and discard files to ensure that the data you think should be loaded is actually being loaded.

Looking for expert Oracle training? We offer Oracle training in the UK and New Zealand for both developers and dbas. Contact us today and discover how we can help you slash costs and explode productivity.

Streamline your Oracle systems with expert Oracle consulting services from Smartsoft. With skills honed to a fine edge over many years with myriad clients in a variety of industries, our consultants have the knowledge and experience to help you. Click here for a consultation application form.