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

I would rather filter out the file first before even passing it to sqlldr. It is wise to do so because the duty of sqlloader shud be to load the data into database as you give it and not to filter your file. Cleaning up should be done before even passing it. Looking at F: it seems you are doing it in Windows environment. In Unix, I would have used an awk command like awk '$3 ~ /^(10|90|30|70)$/ {print} dept.csv >new_dept.csv to filter the records and then load new_dept.csv. Another option is to use External Tables, where you get PREPROCESSOR option to do such manipulations. — Kaushik Nayak
– Kaushik Nayak, Commented May 1, 2018 at 7:01
Thank you for your suggestion. Yes currently I am using Windows environment. can you please suggest how can i filter this large amount of data in windows environment? I never use External table. let my try with that. — SwapnaSubham Das
– SwapnaSubham Das, Commented May 1, 2018 at 12:28

GolezTrol · Accepted Answer · 2021-11-10 15:25:14Z

2

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
)

edited Nov 10, 2021 at 15:25

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

answered May 1, 2018 at 12:01

galmeriol

4614 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SwapnaSubham Das Over a year ago

above script is only work for dept 10, other dept(90,30,70) are discarded.

galmeriol Over a year ago

I edited the post with POSITION(1) addition for each INSERT INTO statement. Please check whether this version works.

SwapnaSubham Das Over a year ago

Yes it's working now.Thank you very much. Can you please explain me about position(1) or refer me any documentation. As per my knowledge position is used for fixed length flat file with proper position like col_name position(start_position : end_position).

galmeriol Over a year ago

It forces SQL*Loader to start loading from certain location in data. You can check the following reference for more information. docs.oracle.com/cd/E11882_01/server.112/e22490/…

Collectives™ on Stack Overflow

How to use multiple conditions in sql loader?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related