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

Example 3 for SQL*Loader

This example shows how to selectively insert records according to a criteria.
First, the table to be filled is created: 
create table sql_loader_3 (
  field_1       varchar2(10),
  field_2       varchar2(10)
);
Here's the control file. It specifies that records whose second field equals Fruit is loaded.
load_3.ctl
load data
infile 'load_3.dat' "str '\r\n'"
discardfile 'load_3.dsc'
insert
into table sql_loader_3
when field_2 = 'Fruit'
fields terminated by ';'
(
  field_1      char,
  field_2      char
)
Here's the data. The name of the file (load_3.dat) had been specified with the infile statement in the control file.
load_3.dat
Banana;Fruit;
Lemon;Fruit;
Avocado;Fruit
Ford;Car;
Pear;Fruit;
Apple;Fruit;
The following command actually loads the data. 
sqlldr control=load_3.ctl userid=rene/rene
The table now contains (select * from sql_loader_3): 
FIELD_1    FIELD_2
---------- ----------
Banana     Fruit
Lemon      Fruit
Avocado    Fruit
Pear       Fruit
Apple      Fruit
One record didn't meet the criteria. It appears in the discard file:
load_3.dsc
Ford;Car;