sravan kumar sravan kumar - 21 days ago 10
Scala Question

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

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 the 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 Source

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.