SQL Loader with condition - example

Just follow the steps below. You will understand how to do SQL loader with condition. Condition can be written in the loader controlfile with WHEN clause. I am using scott as the working schema

1. Create a table in the SCOTT schema

 create table aaa (

   field1       varchar2(10),

   field2       varchar2(10)  

 );

  

2. Create a file data.dat  in /u01/ with the following contents. 

  

Banana;Fruit;

Lemon;Fruit;

Avocado;Fruit;

Ford;Car;

Pear;Fruit;

Apple;Fruit;

3. Create the controlfile control.ctl in   /u01/  with the following contents 

load data

infile '/u01/data.dat'

DISCARDFILE '/u01/emp.dsc'

insert

into table aaa when field2='Fruit'

fields terminated by ';'

(

  field1      char,

  field2      char

)

4. Run the SQL*Loader as given below

  

sqlldr control=/u01/control.ctl userid=scott/tiger

5. Check the discard file as well as the table scott.aaa to verify we have done what we were supposed to do ..