Importing data with SQL*Loader

Oracle created a tool for loading large amounts of data into an Oracle database. The tool is called SQL*Loader and can be run by using the command sqlldr.

In this example, we load data from a Microsoft Excel sheet. Before we can do that, we have to save the sheet as CSV (Comma Separated Values). Next, we define the structure of the data in a CTL-file, which also contains configuration data for the import process.

An example CTL-file (import.ctl):

options (errors=100, discard='import.discards')
load data
infile 'import.csv'
badfile 'import.errors'
insert
into table import_table
fields terminated by ','
optionally enclosed by '"'
(
    COL1,
    COL2
)

In the configuration above, we load the data in the table "import_table". The table has two columns: COL1 and COL2. The CSV-file has two values on every line, which are separated by a comma.

This configuration has two options defined:

• errors=100
  
• discard=import.discards
  

This means that the load is aborted after 100 errors, and the discarded lines are saved in the import.discards.

Other configuration settings:

• infile: the file to load
  
• badfile: the file to save errors when they occur
  
• insert: insert data in empty table. Other modes: append, replace, truncate
  

We can start the load process by executing the following command:

sqlldr <user>/<password>@<host>
       control=import.ctl log=import.log

References:

• http://www.orafaq.com/wiki/SQL*Loader_FAQ
  
• http://psoug.org/reference/sqlloader.html