File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes select with an Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of JavaScript Promises Essentials this week in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "select with an "in" clause..." Watch "select with an "in" clause..." New topic
Author

select with an "in" clause...

hernan silberman
Greenhorn

Joined: Nov 20, 2001
Posts: 27
I need to do a select that looks like this:
select C1, C2, C3 from my_table
where id in ( v1, v2, v3, ..., vN );
The values for the in clause are arbitrary and could be so many that I worry the length of the resultant SQL string could exceed Oracle's limit, if I were to naively generate the String in my Java code.
I've read up on batching but didn't find anything similar for selects of this type. How can I run this query safely using JDBC? I know this is a common thing to do, any help is much appreciated.
thanks...
H
Dana Hanna
Ranch Hand

Joined: Feb 28, 2003
Posts: 227
While this approach in reasonable in some instances, if you are worried about exceeding a limit (is there one?), we'd have to know more about why you are doing this.
Perhaps there is a better way.
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1752
    
    2
I don't know where I'm channeling this from (in other words, verify before coding anything based on what I'm saying), but for some reason I seem to think Oracle has a limit of 1000 (or maybe 1024) items in an "in" list.
Ramesh Donnipadu
Ranch Hand

Joined: Sep 16, 2000
Posts: 100
If there indeed is a limitation, can you divide the number of IDs into chunks of size 1000 (or whatever is the max), select chunks of 1000s and add more OR clauses as in below?

SELECT XXX FROM TABLE WHERE ID IN (I1, I2, ...Ik) OR ID IN (Ik+1,Ik+2, ..I2k) OR ID IN (I2k+1+...) ...
hernan silberman
Greenhorn

Joined: Nov 20, 2001
Posts: 27
No problem... here's the situation:
We have a query tool which allows our users to select a collection of objects from our database based on a simple criteria they provide through our application's user interface. I work at an animation studio, so the user might ask for a list of all shots "done in animation". Shots trickle through the Animation department in an arbitrary order based on complexity, staffing, and numerous other variables.
Our query tool application code is built in such a way that the query it constructs "dumbs down" the request to look like this:
-- Query 1
select shot_id from shots where <programmatically constructed criteria from the query tool UI>;
Once I have this arbitrary list of shot_ids, I need to retrieve a ResultSet from which I can build all of the Shot objects:
-- Query 2
select C1, C2, C3 from shots
where shot_id in ( <the ResultSet from Query 1> );
This is a simplified example, but you get the point. Query 1 and Query 2 are loosely coupled and I can't combine them into one query without having a huge effect on the current codebase. I realize that Query 2 would work as a subquery of Query 1, but I don't have that luxury.
That being said, I am stuck having to run Query 2 once for each row in Query 1's ResultSet, or build a compond string representation of Query 1's ResultSet and construct Query 2 in an ad hoc fashion risking a SQL String that's too big for Oracle.
I'd love to be able to do something like this using JDBC:
PreparedStatement theStmt =
theCon.prepareStatement( "select <cols> from shots where shot_id in ?" );
theStmt.execute("CREATE TYPE shotidlist_type AS table OF number(8)");
oracle.sql.ArrayDescriptor theArrayDesc =
oracle.sql.ArrayDescriptor.createDescriptor( "shot_idlist_type", theConnection );
java.sql.Array theArray =
new oracle.sql.ARRAY( theArrayDesc, theConnection, theShotIds /*int []*/ );
theStmt.setArray( 1, theArray );
ResultSet theResult = theStmt.executeQuery();
... but I've had no luck with this and the more I think about it, I realize that what I'm trying to do is a fairly special case. I'm starting to wonder if it's possible at all?
Any help is greatly appreciated, thanks for reading this far ;-)
hernan
hernan silberman
Greenhorn

Joined: Nov 20, 2001
Posts: 27
One more update, from my Oracle server:
WARNING: java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
at com.dw.nile.services.sql.test.JDBCTest.runTests(JDBCTest.java:51)
at com.dw.nile.services.sql.test.JDBCTest.<init>(JDBCTest.java:28)
at com.dw.nile.services.sql.test.JDBCTest.main(JDBCTest.java:122)
hernan silberman
Greenhorn

Joined: Nov 20, 2001
Posts: 27
Figured I'd post a follow up, as this issue turned out to be a difficult one to research even with the almighty google.com at my disposal.
My DBA and I came up with this solution:
1) Create a temp table in Oracle:
create global temporary table bulk_select(value01 varchar2(255));
In our Java code we do these "in" queries in the following fashion:
String theSQL = "select C1, C2, ..., Cn from table where Cy in (select * from bulk_select)";
// Do an array insert of the keys into bulk_select.
// run theSQL via JDBC like a normal query.
This solution is a bit Oracle-specific as the temporary table implementations on other RDBMSs have different semantics than oracle. In oracle, the temporary table is tied to the session, so all of the rows inserted by a given session are visible only to that session. That makes this work since the select has to become an insert followed by a select.
Thanks for the advice, this worked out splendidly.
Hernan
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: select with an "in" clause...