Some SQL Loader basics - datafile to column mappings

I've had cause this week to dig out some old scripts for sqlloader just to demonstrate some of the basic functionality of how values from a datafile relate to the columns they load into in the database.

Below are 2 simple test cases that demonstrate 2 things:

1) How column order in the datafile does not have to match the column order in the database
2) How to load the same value from a datafile multiple time

So for the 1st question lets set up a simple table with two columns - col1,col2 in that order

SQL> create table demo (col1 number, col2 date);

Table created.

My datafile has the data i need but is the other way round col2,col1 - so how do i deal with that?

# cat loader.dat
10-JAN-2014,1
11-JAN-2014,2

Well it's very easy - the way that sqlloader is working here is that you just need to tell it a list of columns - the order of these columns should match the order of the data in the datafile - not the order of the columns in the table. Below you are essentially saying the first csv value load into col2, the second csv value load into col1 (and so on if there were more columns)

# cat loader.ctl
  LOAD DATA
   INFILE loader.dat
   INTO TABLE demo
   FIELDS TERMINATED BY ","
   (col2,
    col1)

So lets load it in to prove it

# sqlldr / control=loader.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Tue Dec 9 12:35:28 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 2

It looks fine - otherwise it would have errored with datatype mismatch or something like that


And then select the data back - and you can see it's fine

SQL> select * from demo;

      COL1 COL2
---------- ---------------
         1 10-JAN-14
         2 11-JAN-14

 Simple eh - so lets deal with point 2)

 So in my artificial demo i have a 4 columns table - see below and a datafile with only one 'column'
 
SQL> create table demo (col1 number,col2 number,col3 number,col4 number);

Table created.

cat loader.dat
1
2
3
4

So how do i deal with that i want to do stuff with 'column' 1 from the datafile and have it appear in all 4 of the table columns

Well here it is - a control file to do just that - the trick here is the use of the :col1 value which is the current value of col1 when it was read in from the datafile - we can do with this what we like. (note i had to add TRAILING NULLCOLS as there values were missing from the datafile and oracle was not happy with that)

cat loader.ctl
  LOAD DATA
   INFILE loader.dat
   INTO TABLE demo
   FIELDS TERMINATED BY "," TRAILING NULLCOLS
   (col1,
    col2 ":col1",
    col3 ":col1*99",
    col4 ":col1 /2")

So lets load the datafile in

sqlldr / control=loader.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Wed Dec 10 09:07:30 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

 And check what it looks like

SQL> select * from demo;

      COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
         1          1         99         .5
         2          2        198          1
         3          3        297        1.5
         4          4        396          2

Job done!

So sqlldr stuff is pretty easy (and quite powerful), however it has been superceded by external tables now. These offer even more flexibility and performance than sqlldr and i would encourage to use them instead