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


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