queryForRowSet doesn't return results with null in optional date parameter

Question

I added optional date parameters to several Oracle SQL/Code which worked except from specific SQL used in combination with queryForRowSet

My SQL with optional date:

SELECT * from I where  I.MYDATE <= nvl(?, I.MYDATE)

Code sending null for optional date:

Object[] param= new Object[] {null};
SqlRowSet result = jdbcTemplate.queryForRowSet(QUERY, param);

If I remove parameter SQL return results, but with null it doesn't return results

Answer 1

You need to provide information about the type of the null:

Object[] param = { null };
int[]    paramType = { java.sql.Types.DATE };

SqlRowSet result = jdbcTemplate.queryForRowSet(QUERY, param, paramType);

Or, it should work when the parameter type is explicitly specified this way (as you have only one parameter for the query):

java.sql.Date[] param = { null };
SqlRowSet result = jdbcTemplate.queryForRowSet(QUERY, param);