• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3354
  • Last Modified:

Spring JDBC - Oracle Query issue ("IN" parameter)

I have an inner query where it has to search through the list of IDs (using IN parameter).  If the size of list of IDs is more than 1000, sql gives error of exceeding the maximum number of expressions in the list. I am using Spring JDBC.
My Query:
SELECT id, name, distance, type,  FROM GLBL_NAME gname GLBL_DEF def WHERE gname.id = def.id and gname.OID in(2508, 1701, 4210, . . . . more than 1000)

above query is giving :
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT id, name, distance, type,  FROM GLBL_NAME gname GLBL_DEF def WHERE gname.id = def.id and gname.OID in(2508, 1701, 4210, . . . . more than 1000) nested exception is java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000
0
glimlach
Asked:
glimlach
1 Solution
 
glimlachAuthor Commented:
your quick solutions or alternative implementation solution for this scenario will help me alot..
Thanks in advance..
0
 
johnsoneSenior Oracle DBACommented:
1,000 items is all that Oracle allows to have in an in list.  It is a hard limit.

The only way that I know around this is to use a subquery instead of a list.  That will allow more than 1,000.

SELECT id, name, distance, type,  FROM GLBL_NAME gname GLBL_DEF def WHERE gname.id = def.id and gname.OID in(select....)

If that list is not available by a query, you can create a global temporary table that holds the list and use that in the subquery.
0
 
sdstuberCommented:
you can do it in two ways.

First,  build your string with 2 or more  IN clauses that are or'd together



and
(
 gname.OID in(1,2,3,4....1000)
or
gname.OID in (1001,1002,...,2000)
or
gnamd.OID in (2001,2002,...,3000)
or
....
)

inserting your own list of 1000 id's at a time in each or clause until you exhaust your array of id's.


other way,

insert your data into a global temporary table.  then use a sub-query in your IN clause.


SELECT id, name, distance, type,  FROM GLBL_NAME gname GLBL_DEF def WHERE gname.id = def.id and gname.OID in (select id from your_temp_table)



0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sdstuberCommented:
or last way....

use a pipelined function that will accept a collection (array) of id's and return a pipelined collection to be used a subquery.  this is effectively the same idea as using the temp table

SELECT id, name, distance, type,  FROM GLBL_NAME gname GLBL_DEF def WHERE gname.id = def.id and gname.OID in (select id from table(your_pipeline_function(:your_array_of_ids)))

0
 
julianopolitoCommented:
Can´t you use EXISTS instead of  IN ? The EXISTS function searches for the presence of a single row meeting the stated criteria as opposed to the IN statement which looks for all occurrences, that´s why the limit of 1000 exists..... (although it makes no sense for me why to have both behaviours)
0
 
sdstuberCommented:
what do you want to do an exists check against?  You can't do an exists-list like you can an in list.

If you put your data into a temp table or return it with a pipelined function yes, you could use EXISTS.
I'm not sure why you would want to use that instead of IN.  

The IN clause will stop searching as soon as it finds a match.  It won't go through all of the records as you hypothesize.
If you use the pipelined function and you add logging to it you can prove this as the pipeline will stop as soon as a match is found.
0
 
glimlachAuthor Commented:
This works. Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now