• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

maximum number of expressions in a list is 1000 ORACLE

 
Deeps Mistry
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

i have a query like:

select * from xyz_table where ID IN(orgIdList)

I am using ibatis.

I am creating orgIdList in java : select ID from abc_table.

But the problem is whenever orgIdList size exceeds 1000, it throws a SQL error ORA-01795 saying maximum number of expressions in a list is 1000

I googled this problem...what i found out was i could use Global Temporary Table.

I am not able to implement it. Could anyone please guide me as to what should be my approach?


Thanks
 
Scott Selikoff
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As a quick fix, you could split the query into sets of IN statements such as "WHERE X IN (1...1000) OR X IN (1000...). If the items in the list are part of a query you could use TOP/LIMIT to select those items.

As a better fix, don't use nested statements when you can avoid them, joins are FAR superior.
 
pankaj patil
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is the Restriction put by the Database
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34229
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This article addresses it for the JDBC. The concepts still apply.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic