René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Example 2 for SQL*Loader

This example shows how to insert records that are seperated by a comma.
First, the table to be filled is created: 
create table sql_loader_2 (
  field_1       varchar2(10),
  field_2       number
);
Here's the control file. Note, that the second field is specified as integer external. That means, that the numbers in the dat file are human readable rather than "real" bytes.
load_2.ctl
load data
infile 'load_2.dat' "str '\r\n'"
insert
into table sql_loader_2
fields terminated by ','
(
  field_1      char,
  field_2      integer external
)
Here's the data. The name of the file (load_2.dat) had been specified with the infile statement in the control file.
load_2.dat
one,1
two,2
fifty,50
eighty-eight,88
one hundred,100
fifteen,15
The following command actually loads the data. 
sqlldr control=load_2.ctl userid=rene/rene
The table now contains (select * from sql_loader_2): 
FIELD_1       FIELD_2
---------- ----------
one                 1
two                 2
fifty              50
fifteen            15
Two records could not be loaded, they appear in the bad file:
load_2.bad
eighty-eight,88
one hundred,100