Wednesday, April 28, 2010

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'
into table import_table
fields terminated by ','
optionally enclosed by '"'

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


1 comment:

  1. Yes, but can you run it from a J2EE app running in a JBOSS container? That's the trick!