OraFAQ Forum: Server Utilities » sql loader to load in multiple table with IN predicate

Home » RDBMS Server » Server Utilities » sql loader to load in multiple table with IN predicate (10.2.0.5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql loader to load in multiple table with IN predicate [message #554754]

Thu, 17 May 2012 05:06

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Hi Guys,

I have a situation where i have to load data in multiple table using sql loader. I have IN predicate which i don't know is allowed in the sql loader or not

my control file and is as below

LOAD DATA
INFILE 'c: emp\demo05.dat'
BADFILE 'c: emp\bad05.bad'
DISCARDFILE 'c: emp\disc05.dsc'
REPLACE

INTO TABLE emp (
empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL)

--1st project: proj has two columns, both not null
INTO TABLE proj
WHEN deptno in (2,4,8,9) (
emp POSITION(1:4) INTEGER EXTERNAL,
deptno POSITION(25:27) INTEGER EXTERNAL)

-- 2nd project
INTO TABLE proj
WHEN deptno not in (35,36) (
emp POSITION(1:4) INTEGER EXTERNAL,
projno POSITION(29:31) INTEGER EXTERNAL)

i am getting below error when executing above error

SQL*Loader-350: Syntax error at line 5.
Expecting "(", found keyword when.
WHEN DEPTNO IN ('

Please suggest

Report message to a moderator

Re: sql loader to load in multiple table with IN predicate [message #554758 is a reply to message #554754]

Thu, 17 May 2012 05:16

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As far as I can tell, WHEN clause accepts only "=" and "<>" operators. "IN" is not supported.

Therefore, you should split it to
WHEN deptno = 2
WHEN deptno = 4
...
WHEN deptno <> 35
...

Maybe it would be simpler to load data using the external tables feature.

Report message to a moderator

Re: sql loader to load in multiple table with IN predicate [message #554792 is a reply to message #554758]

Thu, 17 May 2012 07:40

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

You can use AND but not OR with WHEN, so you can use:

WHEN deptno != 35 AND deptno != 36

Report message to a moderator

Re: sql loader to load in multiple table with IN predicate [message #554815 is a reply to message #554792]

Thu, 17 May 2012 11:01

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

my list is big for in clause, is there any restriction on control file, i can't used AND because it will do ANDing which i don't want. i simply want to route the data to table based on in list.

I am preparing statement for every in list values.
Thanks

Rajesh

Report message to a moderator

Re: sql loader to load in multiple table with IN predicate [message #554817 is a reply to message #554815]

Thu, 17 May 2012 11:11

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Another method would be to either use SQL*Loader to load all of the data into a staging table or use an external table as a staging table, then use an insert selecting from the staging table with an IN clause. If you use an external table, then your data file must be on your server, not your client.

Report message to a moderator

Previous Topic:	sql loader syntax error 350
Next Topic:	Skip column while loading data using SQL Loader

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:49:25 CST 2025