• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

maximum number of expressions in a list is 1000 ORACLE

 
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It is the Restriction put by the Database
 
author & internet detective
Posts: 41919
910
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This article addresses it for the JDBC. The concepts still apply.
 
reply
    Bookmark Topic Watch Topic
  • New Topic