|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 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.
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 (
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: