GeeCON Prague 2014*
The moose likes JDBC and the fly likes maximum number of expressions in a list is 1000 ORACLE Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "maximum number of expressions in a list is 1000 ORACLE" Watch "maximum number of expressions in a list is 1000 ORACLE" New topic
Author

maximum number of expressions in a list is 1000 ORACLE

Deeps Mistry
Ranch Hand

Joined: Jan 31, 2009
Posts: 189
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

Joined: Oct 23, 2005
Posts: 3710
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
pankaj patil
Ranch Hand

Joined: Dec 19, 2004
Posts: 98
It is the Restriction put by the Database


Regards,
Pankaj Patil
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30596
    
154

This article addresses it for the JDBC. The concepts still apply.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
GeeCON Prague 2014
 
subject: maximum number of expressions in a list is 1000 ORACLE