Home > How to > Oracle SQL*Loader

Oracle SQL*Loader

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle Database and it comes with planty of formating and loading options.

You can use SQL*Loader by executing the sqlldr  utility (sqlload on some platforms).

The format required is :

sqlldr username@server/password control=loader.ctl

Invoking sqlldr without any parameters displays a short help screen.

The loader.ctl is a control file that must be created before starting sqlldr.

Here is a sample loader.ctl file

OPTIONS (SKIP=1)
load data
infile ‘mydata.csv’
TRUNCATE INTO table MY_DATA
fields terminated by ‘;’ optionally enclosed by ‘”‘
TRAILING NULLCOLS
(dummy1 FILLER,
PLDAMRN “trim(:PLDAMRN)”,
ARTIKEL “trim(:ARTIKEL)”,
dummy2 FILLER,
KANTOOR “trim(:KANTOOR)”,
NCTSKANTOOR “trim(:NCTSKANTOOR)”,
BRUTO “replace(:BRUTO,’,’,’.’)”,
NETTO “replace(:NETTO,’,’,’.’)”,
COLLI,
TYPE “trim(:TYPE)”,
NCTSMRN “trim(:NCTSMRN)”,
NCTSLOCATIE “trim(:NCTSLOCATIE)”,
NAAM “trim(:NAAM)”,
POST “trim(:POST)”,
STAD “trim(:STAD)”,
CONTACT “trim(:CONTACT)”,
SID “AUT_CO_SID_SEQ.NEXTVAL”)

The loader will load the comma-separated values file mydata.csv into table MY_DATA.

The comma-separated values file can be created using Excel.

In the example the table MY_DATA will first be truncated and then loaded with the new data.

If you are interested in learning more on SQL*Loader, here is Oracles’s online documentation http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/part_ldr.htm

Oracle SQL*Loader: The Definitive Guide is also a highly recommended book.

Advertisements
Categories: How to
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: