IN clause in PreparedStatement

Question

I am using ojdbc6 jar and I want to use the In clause in a PreparedStatement.

I have used the createArrayOf(String,ArrayOfString) method of the connection object.

I got an error as Unsupported Feature

I have checked the PhysicalConnection class in the implementation for this method is

public Array createArrayOf(String s, Object aobj[]) throws SQLException {
        SQLException sqlexception = DatabaseError.createUnsupportedFeatureSqlException();
        sqlexception.fillInStackTrace();
        throw sqlexception;
    }

I have checked the ojdbc14.jar for this method. In that .jar file this method is not defined.

Please help me, which jar do I have to use? I want to implement the In clause functionality in the PreparedStatement.

Please post your error message. Also, what Oracle version are you connecting to? What is the SQL string you are using in the PreparedStatement? Please include more information — Ascalonian, Jan 29 '15 at 13:25
Also, why don't you just say "AND some_value IN ('value1', 'value2')" right in the SQL for the PreparedStatement? — Ascalonian, Jan 29 '15 at 13:28
Once you post the other information into your question, we can help you more :-) — Ascalonian, Jan 29 '15 at 13:30
see my query somthing like this... Select * from Employee where ID IN(?). I have tried to set this holder with Array Of the oracle... like Array ids=connection.createArrayOf("VARCHAR",new String[]{"0","1"}); prestmt.setArray(1,ids); — user2343420, Jan 29 '15 at 13:33
`IN` clause _does not_ support `bind array`.. instead you have to construct the query like `IN (:val1,:val2...:valn)` — Maheswaran Ravisankar, Jan 29 '15 at 13:35
And I am getting this error java.sql.SQLException: Unsupported feature at oracle.jdbc.driver.PhysicalConnection.createArrayOf(PhysicalConnection.java:9309) — user2343420, Jan 29 '15 at 13:36
ok but i dont know how many numbers of values will be there.. I canonot make it dynamic — user2343420, Jan 29 '15 at 13:37
So you get an String array of the values and it can be different sizes? — Ascalonian, Jan 29 '15 at 13:39
Yes I am getting List from and I am creating Array of it and one more i cant touch the Query part as well because placeholder defined is constants accourding to requirnment — user2343420, Jan 29 '15 at 13:41
How can it be a constant if the number of values can change? — Ascalonian, Jan 29 '15 at 13:41
you can make it dynamic.. simply with generating variable names.. unfortunately, Oracle *DONOT* support binding array to IN clause with SQL. There's a `PL/SQL` Solution for this, which might be cumbersome. — Maheswaran Ravisankar, Jan 29 '15 at 13:42
I have read the answer from the stack overflow only http://stackoverflow.com/questions/13254133/jdbc-prepared-statement-how-to-set-a-list --> It seems the best answer in JDBC 4 is to use setArray(). PreparedStatement pstmnt = dataSource.getConnection().prepareStatment( "SELECT * FROM Table WHERE id NOT IN ( ? )" ); pstmt.setArray( 1, dataSource.getConection().createArrayOf( "string", excludeList.toArray() ) ); — user2343420, Jan 29 '15 at 13:43
Did you look at this protected post? http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternativesc — Ascalonian, Jan 29 '15 at 13:45
Few Other Database support this. Oracle, don't. Hence this runtime exception. — Maheswaran Ravisankar, Jan 29 '15 at 13:48
ohh ok... yes I have check that protected post as well... I thought by upgrading driver or other method solution will be there but no luck.. ok — user2343420, Jan 29 '15 at 13:56
Thank you friends for your valuable time,.. please let me know if there any other solution .. I have to execute the preparedstatment number of time as number of IDS are there,, this is only back up solution.... — user2343420, Jan 29 '15 at 13:58
The only approach that worked for me. Unfortunately not as clean as createArrayOf - http://blogs.itemis.de/kloss/2009/03/05/arrays-preparedstatements-jdbc-and-oracle/comment-page-1/ — user43685, Aug 13 '15 at 18:57

score 0 · Answer 1 · edited Jan 29 '15 at 13:59

So you want to do something like...

SELECT * FROM TAB WHERE ID IN ?

I had a similar problem in the past and tried all sorts of tricks with JDBC and Oracle (such as user defined types etc), but this is what we ended up with...

Create a global temporary table containing a single ID column (scoped to the session - ON COMMIT DELETE ROWS)
Start a transaction
Insert all of the Ids you want using a batch statement into the temp table
Join to this table to get the results, for example...

SELECT * FROM TAB T INNER JOIN MyTempTable X ON T.ID = X.ID
When you commit, the temp table is truncated

If you need to do several of these in the same transaction, be sure to delete form the temp table first.

(Also, we had loads of problems with driver versions and things like setArray etc, so we settled on this)

@user2343420 that's a pain!! If you have to resort to "dynamic" sql, beware that some DBMS (such as Oracle) only support a maximum of 1,000 items in an IN clause. You could always prepare 1,000 statements and if you have more than 1,000 things, do them in multiple batches... — BretC, Jan 29 '15 at 20:59

IN clause in PreparedStatement

1 Answers1