SQL*Loader – Qucikly Transfer your legacy data to Oracle

SQL*Loader

By Sandip Davda

What is SQL*Loader?

SQL*Loader has lots and lots of options which can be used to handle various types of data and levels of complexity. SQL*Loader is fully described in the Oracle Server Utilities User’s Guide. This document is just about getting started. SQL*Loader runs on Unix, mainframes, and PC’s. This document is just about running it from a Windows PC.

Why Use SQL*Loader From Your PC?

If you need to transfer quite a lot of data from your machine to an Oracle database table, you might want to use SQL*Loader. If you already have the data in some other format, it may be worthwhile to use SQL*Loader. If you need to transfer local data to a remote database on some recurring basis, it may be preferable to use SQL*Loader rather than something like FTP.

Getting Started, an Example
Here’s what you do:

1) Create your data file. This is easy. Save your Excel spreadsheet data AS a Comma-Separated-Variable (*.csv) file. This will automatically put commas between each of the four data elements. In addition, if any of the data elements already contain a comma, the Save AS *.csv step will optionally and automatically enclose that data in double quotes. So, after your Save AS command, you might have a file named C:\dept.csv that contains data like this:

21,Production,Bangalore
22,HR,Chennai
23,FMG,Bangalore24,Administration

2) Create your control file. Using any text editor, create a file (say, C:\dept.ctl) containing these lines:

LOAD DATA
INFILE ‘C:\dept.csv’
APPEND
INTO TABLE scott.dept
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ TRAILING NULLCOLS
(deptno integer external, dname, loc )

The append keyword says, “add new rows to existing rows .” There’s also an INSERT [into empty table] and replace [existing rows] option.Because the last two items are of character datatype, it was not necessary to further describe them – character is the default. The first column is numeric data . The INTEGER EXTERNAL describes the datatype in the C:\dept.csv input file. Notice there is some missing data in the data file –Administration has no location defined. The TRAILING NULLCOLS statement handles the missing data; it tells SQL*Loader to load any missing data as NULL values. There are, as i said earlier, lots of available options described in the Utilities User’s Guide.

3) Run SQL*Loader. At an MS-DOS prompt (or the Start, Run menu) , execute SQL*Loader as follows:

sqlldr scott/tiger@UWTEST control=C:\dept.ctl

When the load completes, look in the file C:\dept.log. This log file will contain information about how many rows were loaded, how many rows — if any — were NOT loaded, and other information that may be useful to reassure or debug.