oracle jdbcTemplate Invalid column type

Question

I am using below code while deleting a row from database

jdbcTemplateObject.update("DELETE FROM SUPERVISION_ROOM cl WHERE cl.fk_group IN ? and cl.fk_room IN ?", gourpIds, deleteExamDTO.getRoomIds());

But i getting following exception:

PreparedStatementCallback; uncategorized SQLException for SQL [DELETE FROM SUPERVISION_ROOM cl WHERE cl.fk_group IN ? and cl.fk_room IN ?]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type] with root cause

Answer 1

JDBCTemplate does not support a transparent IN list binding as you try to use it.

It is documented in 11.7.3. Passing in lists of values for IN clause

You would have to either have a number of variations with the desired number of place holders prepared or you would have to dynamically generate the SQL string once you know how many place holders are required.

So basically you must first expand the SQL statement with the right number of placeholders and then pass each element as a separate paramater.

...
WHERE cl.fk_group IN (?,?,?,?) and cl.fk_room IN (?,?)