Oracle jdbc "createArray" throws "Unsupported feature" exception while trying to pass array to prepared statement [duplicate]

Question

I am trying to pass an array to my prepared statement by doing createArrayOf

val prep: PreparedStatement = con.prepareStatement("select * from SOA_WEB_USER.VOPEX where CMF_PPK_NBR in (?)")

val array :Array[Object]=Array("1165006001","1165006002")

val sqlArray = con.createArrayOf("VARCHAR",array) //getting the exception here 

prep.setArray(1,sqlArray)

val rs = prep.executeQuery()

while (rs.next()) {

    println(rs.getObject(1))

}

But createArrayOf method throws an error saying

Exception  thread "main" java.sql.SQLFeatureNotSupportedException:Unsupported feature   
at Oracle.jdbc.driver.PhysicalConnection.createArrayOf(PhysicalConnection.java:8707)
at com.testpackage.Main$.main(Main.scala:109)
at com.testpackage.Main.main(Main.scala)    
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)     
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)

I am using the ojdbc7.jar for the jdbc connection. Is there anything I can do differently to pass the array to the prepared statement?

Answer 1

I am not going to answer the question you've asked ("how can I pass the array to the prepared statement"), because even if you could figure out how to pass the array, your code would quite probably still not work.

The problem is that with JDBC you can't pass an array of two values ("1165006001","1165006002")into the query

select * from SOA_WEB_USER.VOPEX where CMF_PPK_NBR in (?)

and expect it to be interpreted by the database as

select * from SOA_WEB_USER.VOPEX where CMF_PPK_NBR in ('1165006001','1165006002')

as it seems to me you want it to.

If you could pass the array in, the query would return all rows for which the column CMF_PPK_NBR contained a nested table with those two values. Oracle would interpret the array as one value, rather than two. I'm guessing that column has type either VARCHAR2, and so you'd only end up with a type error as Oracle tries to compare an array of strings to a single string.

If you want to pass multiple values in an IN clause, then the simplest way is to build up a SQL string with a number of ? marks, and set values for the parameters individually. In other words, for the example above, the SQL string for two parameters would be

select * from SOA_WEB_USER.VOPEX where CMF_PPK_NBR in (?, ?)

and you would have two calls to prep.setString(...), one for each array element. Similarly, if there were, say, 5 items in the array, you'd build a SQL string with 5 ? marks, and call prep.setString(...) 5 times, and so on.

Answer 2

Oracle Database JDBC drivers do not support Connection.createArrayOf because the Oracle Database does not support anonymous array types. A type ARRAY OF FOO is an anonymous type. The array type has no name though the base type does. Oracle Database does not support anonymous array types. You have to define a named type

TYPE ARRAY_OF_FOO IS TABLE OF FOO;

You can then create an Array by calling

oracleConnection.createOracleArray("ARRAY_OF_FOO", elements);

Answer 3

There's no completely nice way to do that, but still two reasonable ways exist.

Use a hand-crafted in (?,...) clause.

The solution is explained here. The idea is to add as many ?s as threre are array elements, and bind each item individually. This gives you safety of mind because values will be properly translated, escaped, etc.

This sort of defeats the purpose of preparing a statement, unless your arrays are normally the same size.

You can also run out of the allowed query length if you have great many items in the array.

Use a cast and pass the array as a string.

The solution (for PL/SQL) is explained here, but applies to general SQL statements. It boils down to using a clause like

...in (select cast(in_list(?) as some_table_type) from dual)

The parameter here is passed as varchar2, like "1, 2, 3", is parsed as an in-memory table, and is selected from, all inside the in clause.

This allows you to have a query of a fixed length, and possibly efficiently reuse the prepared statement. It also allows you to pass great many items in the array.

It, OTOH, requires you to concatenate and pass the data as a string, which can potentially lead to an SQL injection. I'd still suppose it to be safe if your array values are numeric.