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