SQL-Loader: The Step by Step Basics - Example 2 (fixed-field)

The previous example loaded a CSV data file. In this example we'll look at very simple fixed-field data file, and how to use SQL*Loader to transfer that data into an Oracle table.

The Fixed-Field Data File

With a fixed-field data file, there are no comma separators or enclosing double quote marks. The fields are always found in the same position on each line. The white space between values is composed of spaces -- actual ASCII text characters that look empty. For example, in the data show below, the names are always found in the position 1 to 10 (or we could also include the next 2 spaces for possible larger names and say that the first field extends from 1 to 12).

      The Fixed-Field data file (file name = "Data.txt")

NAME        BALANCE START_DT
Jones, Joe       14 Jan-12-2012 09:25:37 AM
Loyd, Lizy   187.26 Aug-03-2004 03:13:00 PM
Smith, Sam    298.5 Mar-27-1997 11:58:04 AM
Doyle, Deb     5.95 Nov-30-2010 08:42:21 PM
   

When using this type of date file, we'll need to carefully determine the starting and ending position for each column of data. Shown below is our data with a raster of positions from "01" to "46".

      Examining the data by position.

    
     0000000001111111111222222222233333333334444444
     1234567890123456789012345678901234567890123456

     NAME        BALANCE START_DT
     Jones, Joe       14 Jan-12-2012 09:25:37 AM
     Loyd, Lizy   187.26 Aug-03-2004 03:13:00 PM
     Smith, Sam    298.5 Mar-27-1997 11:58:04 AM
     Doyle, Deb     5.95 Nov-30-2010 08:42:21 PM
   

By careful examination we could conclude that the name field spans positions 1-12, the balance values span positions 13-19, and the date spans positions 21-43.

So, as in example #1, lets create a folder somewhere on our PC and put this "Data.txt" in that folder (the data in the 1st box shown above).

The Oracle Table

Make sure you have an Oracle table with the proper fields.

   
create table scott.sql_loader_demo_simple
  ( customer_full_name   varchar2(50)
  , account_balance_amt  number
  , account_start_date   date
  ) ;
   

The Control File

Notice that this control file is a little different from the one we used to load CSV values. The order of the columns in this list isn't as critical -- we have the table column name followed by the positions of where to find that data in our data file. Also we don't need to specify a field separator like the comma, or enclosure symbols like the double quotes. In the same directory with your data file, create this control file and name it "Control.txt".

      The Control file (file name = "Control.txt")

------------------------------------------------------------
-- SQL-Loader Control File
------------------------------------------------------------
options  ( skip=1 )
load data
  infile             'Data.txt'           
truncate into table   scott.sql_loader_demo_simple
  ( customer_full_name     POSITION(  1: 12 )
  , account_balance_amt    POSITION( 13: 19 )   
  , account_start_date     POSITION( 21: 43 )    
                           DATE "Mon-DD-YYYY HH:MI:SS am"
  ) 
     

And, here's a quick description of what the commands in the control file are doing:

   
 A)  options  ( skip=1 )
 B)  load data
 C)    infile               'Data.txt'
 D)  truncate into table   scott.sql_loader_demo_simple
 E)    ( customer_full_name     POSITION(  1: 12 )
 F)    , account_balance_amt    POSITION( 13: 19 )
 G)    , account_start_date     POSITION( 21: 43 )
 H)        DATE "Mon-DD-YYYY HH:MI:SS am"
 I)    )


   Line A   = Skip the header row of the data file.

   Line B   = This is the command to start loading data.

   Line C   = This is the name of your data file.

   Line D   = This is the schema and name of your Oracle table.
              The "truncate" specifies that the existing data in the
              table will be truncated or erased prior to the load.

   Line E-I = This is the comma seperated list of columns to be loaded.
              The position (start and end) of each column's source data
              is specified as shown.

   Line H   = The DATE modifier is needed for the column right above it.
              This can be located at the end of the line or just below it
              as shown.
     

The Batch File

Create the batch file in the same directory as the data file and the control file.

   
@echo off
sqlldr  'scott / tiger @ my_database'  control='Control.txt'   log='Results.log'
pause
   

Run the Load and Observe the Data

Double-click on the batch file and the SQL*Load should complete very quickly. Query the data and it should all be there.

That's it!