Using Oracle's SQL Loader Utility

Level Extreme platform

Subscription

Corporate profile

Products & Services

Support

Legal

Français

Articles

Search:

Using Oracle's SQL Loader Utility

Mark McCasland, June 4, 2001

One of the fastest ways to load bulk quantities of data into Oracle is to use the Oracle utility, SQL Loader. The purpose of this FAQ is to describe how to get your data into Oracle tables as fast as possible.

Summary

Description

To load data using this utility, I use 3 files per table -- a parameter file [.PAR], a control file [.CTL] and a data file [.DAT]. The data file contains records whose fields are usually separated by commas and delimited by some character. I typically use a tilde [~] to delimit all my fields. Example .DAT file:

1,~Smith~,~John~,~500 Main St~,~Dallas~,~TX~,~75202-0500~,20001231
2,~Smith~,~Kelly~,~500 Main St~,~Dallas~,~TX~,~75202-0500~,19990704
3,~Smith~,~Mike~,~500 Main St~,~Dallas~,~TX~,~75202-0500~,19990704

You specify various information, including connection and file parameters, in the parameter file:

userid=some_id/user_password@host_name
control=contacts.ctl
bad=contacts.bad
log=contacts.log
discard=contacts.dsc

If you do not specify the bad, log and discard files in the .PAR file, the loader will automatically create them, if necessary, using the same name as the control file name but with the extensions of .bad, .log and .dsc. Bad records are those the loader could not load because the records did not match the structure in the control file or violated an integrity constraint. Discarded records are those that do not meet the criteria of the WHEN clause in the control file. There are other parameters that can be set to integer values that can be specified in the .PAR file: LOAD -- maximum number of records to load [default ALL] SKIP -- how many records to skip at the beginning of the .DAT file [default 0] ERRORS -- how many insert errors to allow before aborting [default 50] ROWS -- how many rows to buffer before issuing a COMMIT [default 64] DISCARDMAX -- how many rows can be discarded before aborting load [default ALL] If you specify ERRORS=0, you can stop the load immediately. Check your .BAD and .LOG files to see what record was a problem. In the .DAT file, you can correct the data or remove the record. Next, edit the .PAR file to SKIP the number of records already loaded. Edit the .CTL file to do an APPEND if not already appending. Finally, restart the loader to pick up where it left off. An additional parameter can be used to speed up loading -- DIRECT=TRUE. This will temporarily disable [placed in a DIRECT LOAD state] any indexes on the table during the load. After the load the indexes will be re-synchronized with the table. You can not use this method if any functions are being performed on the values being read from the .DAT file. What this option does is create pre-formatted data blocks and inserts those blocks into the table. The control file is where you provide information regarding the Oracle table and how to add records. You can either: INSERT rows into an empty table [otherwise aborts] APPEND rows to the table REPLACE everything in the table [DELETE privilege on table is required] In general, I always delete [or truncate] all rows from the tables myself instead of doing a REPLACE. That way, I am assured I am starting with empty tables. For tables already being used in a production environment, you would use APPEND.

LOAD DATA
INFILE contacts.dat
INSERT
  INTO TABLE contacts
  [ WHEN condition1 [ AND condition2 ] ... ]
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '~'
  TRAILING NULLCOLS
     (KEYID,
      LASTNAME,
      FIRSTNAME,
      ADDRESS,
      CITY,
      STATE,
      POSTALCODE,
      LAST_UPDATE DATE (8) "YYYYMMDD")

Now all that is left to do is start the loading process. The syntax for the above example files would be:

SQLLDR parfile=contacts.par

SQLLDR80 parfile=contacts.par

Mark McCasland

More articles from this author

Configuring Your System for Access to Oracle

Mark McCasland, January 1, 2001

More and more we are seeing larger companies migrating their databases to Oracle. Usually this is done for data warehousing and for publishing data to the Internet or a company intranet. What I attempt to explain in this FAQ is how to configure a Windows 3.1 or Win95 workstation to access the Oracle...

Displaying SQL Progress Bar

Mark McCasland, September 1, 1999

Use the following to display the SQL progress bar without having the results [i.e., ### records selected in ### seconds] displayed on the screen or active window.

Hacker's Guide to Visual FoxPro 7.0

Mark McCasland, December 1, 2002

Authors: Tamar Granor, Ted Roche, Doug Hennig and Della Martin Publisher: Hentzenwerke ISBN: 1-930919-22-0 Retail Price: $69.95US for the printed book or $59.95US for the electronic [CHM] file only Publication Date: 2002 Pages: 372 printed, 4.1MB CHM file You also get the electronic ...

How Do I Get a List of All Tables that a Field Occurs in from the DBC?

Mark McCasland, February 6, 2001

If you have a field that is used in multiple tables, you can get a list of all DBC member tables that the field occurs in by executing the following SQL command: select a.objectid, a.parentid, a.objectname fname, b.objectname tname, b.objectid tableid from MyDBC.DBC a join MyDBC.DBC b on a.parent...

Providing Toolbars in Your App

Mark McCasland, December 9, 1999

Let's say you need to provide your users the Report Preview or Report Designer toolbars with your application. You need to provide your users with FOXUSER.dbf and .fpt. The only records you need to include in FoxUser are those whose ID = "TTOOLBAR". Assuming you have a folder where you place all ...

Sending a beep to the speaker[s]

Mark McCasland, August 19, 1998

Using the following code to send a beep to the speaker[s]. External speakers and sound card are not required. It makes use of the MessageBeep Windows API function to accomplish it. It accepts a parameter to identify the beep type. Just pass the beep identifier to obtain a different type of beep.

Using Oracle SPs to Insert and Update Data from VFP

Mark McCasland, April 11, 2003

This demo code will connect to Oracle [using your ID and password] to create a table of Presidents, a trigger, a sequence and some stored procedures. The stored procedures can be called to insert and update data in the Presidents table. Three of the methods in the VFP class will pass data values to...

WebRAD: Building Database Applications on the Web with Visual FoxPro and Web Con

Mark McCasland, August 1, 2002

Authors: Harold Chattaway, Randy Pearson & Whil Hentzen Publisher: Hentzenwerke ISBN: 1-930919-07-7 Retail Price: $49.95 US, $74.95 CDN Publication Date: 2002 Pages: 492 Online information: Table of Contents, 8 appendices, Source Code and E-Book URL: http://www.hentzenwerke.com/ ...