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

Posted on 2008-06-17
Last Modified: 2013-12-18
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 = 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 = 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
Question by:glimlach

Author Comment

ID: 21803369
your quick solutions or alternative implementation solution for this scenario will help me alot..
Thanks in advance..
LVL 34

Expert Comment

ID: 21803731
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 = 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.
LVL 73

Expert Comment

ID: 21803736
you can do it in two ways.

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

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

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 = and gname.OID in (select id from your_temp_table)

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 73

Expert Comment

ID: 21803767
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 = and gname.OID in (select id from table(your_pipeline_function(:your_array_of_ids)))

LVL 17

Expert Comment

ID: 21860914
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)
LVL 73

Accepted Solution

sdstuber earned 500 total points
ID: 21862583
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.

Author Closing Comment

ID: 31467971
This works. Thank you.

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 74
JDeveloper 12c for 32 bit 4 84
Bash Script to Analyze Oracle Schemas 11 102
Oracle - SQL Where clause causing Invalid Number Error 4 32
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now