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

Question

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

val array = Array("1165006001","1165006002")
val sqlArray = con.createArrayOf("VARCHAR",array) // getting the exception here 
val prep = con.prepareStatement("select * from SOA_WEB_USER.VOPEX where CMF_PPK_NBR in (?)")
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

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);

---

Edit: As @gouessej mentioned in the comments, you can even use a built-in type in the Oracle Database Data Cartridge extension. It gets even simpler:

val sqlArray = oracleConnection.createOracleArray("SYS.ODCIVARCHAR2LIST", Array("1165006001","1165006002"))
val prep = oracleConnection.prepareStatement("select * from SOA_WEB_USER.VOPEX where CMF_PPK_NBR in (select * from TABLE(?))")
prep.setArray(1, sqlArray)

val rs = prep.executeQuery()
while (rs.next()) {
  println(rs.getObject(1))
}

Answer 2

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 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.