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

Example 4 for SQL*Loader

This example shows how to selectively insert records according to a criteria. It is similar to example 3 but fills two tables.
First, the tables to be filled are created:
create table sql_loader_4_a (
  field_1       varchar2(10),
  field_2       varchar2(10)
);

create table sql_loader_4_b (
  field_1       varchar2(10),
  field_2       varchar2(10)
);
load_4.ctl
load data
infile 'load_4.dat' "str '\r\n'"
discardfile 'load_4.dsc'
insert
into table sql_loader_4_a
when field_2 = 'Fruit'
(
  field_1      position(1) char(8),
  field_2      position(9) char(5)
)
into table sql_loader_4_b
when field_2 = 'City'
(
  field_1      position(1) char(8),
  field_2      position(9) char(5)
)
Here's the data. The name of the file (load_4.dat) had been specified with the infile statement in the control file.
load_4.dat
Banana  Fruit
Lemon   Fruit
Tokyo   City 
Avocado Fruit
Boston  City 
Ford    Car  
Pear    Fruit
Apple   Fruit
The following command actually loads the data.
sqlldr control=load_4.ctl userid=rene/rene
sql_loader_4_a now contains (select * from sql_loader_4_a):
FIELD_1    FIELD_2
---------- ----------
Banana     Fruit
Lemon      Fruit
Avocado    Fruit
Pear       Fruit
Apple      Fruit
sql_loader_4_b now contains (select * from sql_loader_4_b):
FIELD_1    FIELD_2
---------- ----------
Tokyo      City
Boston     City
One record didn't meet the criteria. It appears in the discard file:
load_4.dsc
Ford    Car