[ Prev ] [ Index ] [ Next ]

SQL Loader

Created Wednesday 8 July 2009

SQL Loader is unnaturally fast, largely due to the fact that it sold its soul to the devil back in the early 70's.

1. Loading variable length data

Loading variable length data is by far the simplest approach as the control file can simply specify the attribute names. Even though SQL Loader (sqlldr) permits the data and mapping to be contained in the same file, we will always define control files which point to separate data files as this provides the most flexiblity. In the following example, a mapping is defined for a single table called australia_states, with attributes state_name, population, capital_city, area_sq_km, statehood_date and ranking. Data are loaded from a file called australian_states.csv.

Data file (au_states.csv):

#state_name, short_name,population,capital,area_sq_km,statehood_date, ranking
Victoria,VIC,5246079,Melbourne,227010,1901-01-01 00:00, 1.40
Australian Capital Territory,ACT,340818,Canberra,2358,1901-01-01 00:00, 2.04
New South Wales,NSW,6926990,Sydney,800628,1901-01-01 00:00, 1.395
Queensland,QLD,4228290,Brisbane,1723936,1901-01-01 00:00, 0.589
Northern Territory,NT,217559,Darwin,1335742,1901-01-01 00:00, 0.55
Western Australia,WA,2130797,Perth,2526786,1901-01-01 00:00, 1.21
South Australia,SA,1591930,Adelaide,978810,1901-01-01 00:00, 1.01
Tasmania,TAS,495772,Hobart,64519,1901-01-01 00:00, 0.935
Ashmore and Cartier Islands,,0,Darwin,5,1931-07-23 00:00,0.2
Coral Sea Islands Territory,,0,Kingston,3,1969-09-30 00:00, 0.001
Australian Antarctic Territory,AAT,0,Mawson,5896500,1961-06-23 00:00, 0.9

Control file (au_states.ctl):

LOAD DATA
-- INFILE 'au_states.csv'            -- can specify here or via sqlldr DATA keyword
-- BADFILE 'au_states.bad'           -- can specify here or via sqlldr BAD keyword
-- DISCARDFILE 'au_states.dsc'       -- can specify here or via sqlldr DISCARD keyword
REPLACE                            -- or INSERT, APPEND, TRUNCATE
INTO TABLE au_states
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    state_name      char,
    short_name      char,
    population      integer external,
    capital         char,
    area_sq_km      integer external,
    statehood_date  "to_date(Trim(:statehood_date),'YYYY-MM-DD HH24:MI')",
    ranking         char "to_number(:ranking, '99.999')"
)

SQL DDL for au_states table. This must exist prior to running the loader:

create table au_states (
    state_name varchar2(32),
    short_name varchar2(16),
    population integer,
    capital varchar2(32),
    area_sq_km integer,
    statehood_date date,
    ranking float
)

SQL*Loader execution (skips past first line of field names in data file):

The following commits 100 records at a time, loading data from the named file.

bash $ sqlldr CONTROL=au_states.ctl DATA=au_states.csv LOG=au_states.log BAD=au_states.reject DISCARD=au_states.discard USERID=foo/bar@sid skip=1 rows=100

2. Loading Fixed length data

Todo

3. Oracle Documentation

Backlinks: