Oracle provides the utility SQL*loader for loading data from non-Oracle databases.  SQL*Loader is very flexible and can read a large range of flat files.  SQL*Loader can perform the following functions:

 

1. Load data across network if files on different machine.
2. Load data from multiple files in same session.
3. Load data into multiple tables in same session.
4. Specify character set.
5. Selectively load data by values.
6. Manipulate data before loading.
7. Generate unique sequential keys.
8. Use OS to access data files.
9. Load data from disk, tape or named pipe.
10. Load arbitrarily complex object-related data.
11. Use secondary data files for loading LOBs and collections.
12. Use conventional, direct path or external table loads.

 

SQL*Loader can be used with or without control file and you may encounter the following files during use:

• Control – Defines data format and control behaviors.
• Data – One or more data files containing info to be loaded.
• Log – Logs performance and errors.
• Bad – Contains all records not loaded because of errors.
• Discard – Contains all records discarded by control file identified bypass.

 

SQL*Loader is invoked with the command SQLLDR.  You use parameters in the PARFILE or at the command line to control actions of SQLLDR.  If parameter appears in PARFILE and command line, command line is used.  Three sections of control files: PARFILE.

 

1. Session wide info: global options, input data file location, data to be loaded.
2. Contains INTO TABLE blocks (one or more)
3. Optional third section can contain input data.

 

SQL*Loader can use two methods for loading data: Conventional Path and Direct Load option.  In Conventional Path SQL*Loader creates INSERT statement for data to be loaded and passes it to SQL Parser.   In Direct Load Path SQL*Loader bypasses the SQL and parser and loads data straight into the target table.  Direct Path is much faster than Conventional path, but conventional path is more flexible.

There are a few restrictions of Direct Path method:

 

1. Cannot run concurrently with other transactions against a target table.
2. Triggers on table do not fire.
3. Data is written above high water mark, even if space is available below.
4. Clustered tables are not supported.
5. Foreign Key constraints are disabled during load.

 

 

Larry Catt

OCP