Using SQL*Loader from your PC

Using Oracle SQL*Loader from Your PC

produced and supported by
DRMT	Data Resource Management Technology
DoIT	Division of Information Technology UW - Madison

What is SQL*Loader?

SQL*Loader is Oracle’s utility program for loading data into an Oracle table.

Most often, SQL*Loader takes two input files – a control file and a data file – and loads the data into a single Oracle table. The data file contains data, and the control file contains information about the data -- where to load it, what to do if something goes wrong, etc.

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. At the end of this document, there is a brief comparison of FTP versus SQL*Loader.

Getting Started, an Example

Say, for example, that you’ve got an Excel spreadsheet with State data already in it. You’ve got 50 rows of data – each containing the State Abbreviation, State Name, an [optional] unofficial State Slogan, and the number of State Residents Who Drink Bottled Water.

(Is 50 rows of data really sufficient to justify this exercise? That’s debatable, but let’s say you’ve thought it over and you DO want to SQL*load your data into a 4-column Oracle table at UW-Stevens Point. The remote table is called sp.mystates.)

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:\MyStates.csv that contains data like this:

AR,Arkansas,We are sure proud of Bill,0
CO,Colorado,,3000
WI,Wisconsin,Rose Bowl Champions Again!,5
CA,California,"Dude? You want, like, another hit of Oxygen?",90203049

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

LOAD DATA
INFILE 'C:\mystates.csv'
REPLACE
INTO TABLE sp.mystates
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(State_Abbrev,
State_Name,
State_Slogan,
Nbr_Residents_WDBW INTEGER EXTERNAL)

The REPLACE keyword says, "remove any existing rows before starting the load." There's also an INSERT [into empty table] and APPEND [to existing rows] option.

State_Abbrev, State_Name, State_Slogan, and Nbr_Residents_WDBW are the actual column names defined in the sp.mystates table.

Because the first three items are of character datatype, it was not necessary to further describe them – character is the default. The fourth column is numeric data – it totals the number of state residents who drink bottled water. The INTEGER EXTERNAL describes the datatype in the C:\mystates.csv input file.

Notice there is some missing data in the data file -- Colorado has no state slogan. The TRAILING NULLCOLS statement handles the missing data; it tells SQL*Loader to load any missing data as NULL values. There are, as we said earlier, lots of available options described in the Utilities User’s Guide.

3) Run SQL*Loader.

Prerequisites:

You must have SQL*Loader and SQL*Net installed on your machine. The SQL*Loader program may have a version number included as part of its name, something like sqlldr73.exe or sqlldr80.exe. Or maybe it will be just sqlldr.exe. You can look for it in your ORAWIN95 or ORANT \BIN directory. If it's not installed, you can get the Oracle Client Software installation CD and install "UTILITIES".

You must have the target database (say, it’s called ‘UWTEST’) configured as SQL*Net service in your local tnsnames.ora file. This is pretty standard stuff; it’s probably already there.

You must have authorization to modify the sp.mystates table (INSERT, or DELETE and INSERT if you're using the REPLACE option in the control file. In the example below we assume that user SCOTT with password TIGERhas appropriate authorization.

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

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

When the load completes, look in the file C:\mystates.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.

SQL*Loader versus FTP

File Transfer Protocol (FTP) has often been the first solution that pops to mind whenever there’s a need to move data from one machine to another. In some cases, however, SQL*Loader may provide the better solution.

FTP has one primary strength -- it moves flat files very quickly between machines. Apart from that, it doesn’t do much.

If the goal is to transfer a flat file and put it into an Oracle database table, SQL*Loader does the whole job. Using FTP accomplishes only 1) data transmission. Then, on the receiving end, some process must 2) recognize that a file has arrived, and 3) load the Oracle table (again SQL*Loader, or some custom-written program). By running SQL*Loader over the network, there is just a single step.

In addition, SQL*Loader provides a much better log of what was accomplished, showing how many rows of data where inserted, how many were rejected, reasons for rejecting, and proof of completion. The sender receives much more accurate and complete information that his data submission has been accepted.

Security is also enhanced using SQL*Loader. UNIX accounts are not required on the destination machine, and the inbound FTP service may be disabled entirely. Security-conscious administrators of the destination box may strongly prefer SQL*Loader over FTP.

A Tiny Performance Experiment

In performing a simpler task, FTP does transmit data much faster than SQL*Loader over the network. How much faster? Here is one simple benchmark experiment:

We created a flat-file of 210,000 records and transferred it using FTP from a Pentium 133 to a typical UNIX database server. The FTP completed in 49 seconds.

We then loaded the same 210,000 records over the network into an Oracle table using SQL*Loader. This completed in 9 minutes and 33 seconds (573 seconds). So this little experiment showed FTP to be about 12 times faster. We draw the conclusion that for transferring massive amounts of data, FTP may be absolutely required. Meanwhile, for small to medium amounts of data, SQL*Loader may provide a fuller and sufficiently fast solution.