Home » RDBMS Server » Server Utilities » sql loader to load in multiple table with IN predicate (10.2.0.5)
sql loader to load in multiple table with IN predicate [message #554754] Thu, 17 May 2012 05:06 Go to next message
guddu_12
Messages: 219
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 21580
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.
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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

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 Go to previous messageGo to next message
guddu_12
Messages: 219
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
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 Go to previous message
Barbara Boehmer
Messages: 8922
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.
Previous Topic: sql loader syntax error 350
Next Topic: Skip column while loading data using SQL Loader
Goto Forum:
  


Current Time: Mon Aug 10 23:47:00 CDT 2020