Search notes:
Oracle: SQL Loader Example: Selectively insert records according to a criteria
This example sort of uses a where condition (when ... = ...) to select the records that need to be loaded.
Create destination table
Create the table that is to be filled:
create table tq84_sql_loader_3 (
field_1 varchar2(10),
field_2 varchar2(10)
);
Control file
The control file specifies that only records need to be loaded whose 2nd field's value is fruit.
load data
infile 'load_3.dat' "str '\r\n'"
discardfile 'load_3.dsc'
insert
into table tq84_sql_loader_3
when field_2 = 'Fruit'
fields terminated by ';'
(
field_1 char,
field_2 char
)
The data file
The name of this data file is specified with the infile statement in the control file.
Banana;Fruit;
Lemon;Fruit;
Avocado;Fruit
Ford;Car;
Pear;Fruit;
Apple;Fruit;
Load the data
$ sqlldr control=load_3.ctl userid=rene/password@ora.server.xy
Discarded records
One record didn't meet the load criterion. It is written into the discarded file load_3.dsc:
Ford;Car;