How to use multiple conditions in sql loader?

Question

I have one csv file with below format.First column is id second column is name and third column is dept. There are more than 700k rows in this file. I was trying to move only department 10, 90, 30 and 70 details in a oracle table by using SQL Loader.

100,AAA,10
101,BBB,10
102,CCC,20
103,DDD,30
104,EEE,40
105,FFF,50
106,GGG,70
107,HHH,60
108,III,20
109,JJJ,80
110,KKK,90
111,LLL,90
112,MMM,50
113,NNN,80
114,OOO,10

My table format is:-

create table DEPT_LOADER(
    ID NUMBER
    ,NAME VARCHAR2(100)
    ,DEPT number
);

and below is the control file

    load data
    infile 'F:\SQL_Loader\dept.csv'
    badfile 'F:\SQL_Loader\dept.bad'
    discardfile 'F:\SQL_Loader\dept.dsc'
    insert 
    into table DEPT_LOADER
    when dept = '10' or  dept = '90' or dept = '30' or  dept = '70'
    fields terminated by ','
    (id,name,dept)

but oracle didn't allow "or" operator in when clause. I tried with "in" clause and getting same type of error.

    SQL*Loader-350: Syntax error at line 7.
    Expecting "(", found "or".
    when dept = '10' or  dept = '90' or dept = '30' or  dept = '70'

Please help me on that. how can i use more than one condition in control file

Answer 1

SQL*Loader does not allow OR operator in WHEN clauses. You should use multiple INSERT INTO DEPT_LOADER .

Your control file should be like;

LOAD DATA
INFILE 'F:\SQL_Loader\dept.csv'
BADFILE 'F:\SQL_Loader\dept.bad'
DISCARDFILE 'F:\SQL_Loader\dept.dsc'
INSERT 
INTO TABLE DEPT_LOADER WHEN DEPT = '10'
FIELDS TERMINATED BY ','
(
ID POSITION(1),
NAME,
DEPT
)
INTO TABLE DEPT_LOADER WHEN DEPT = '90'
FIELDS TERMINATED BY ','
(
ID POSITION(1),
NAME,
DEPT
)
INTO TABLE DEPT_LOADER WHEN DEPT = '30'
FIELDS TERMINATED BY ','
(
ID POSITION(1),
NAME,
DEPT
)
INTO TABLE DEPT_LOADER WHEN DEPT = '70'
FIELDS TERMINATED BY ','
(
ID POSITION(1),
NAME,
DEPT
)