How to set large string as param without getting ORA-01460: unimplemented or unreasonable conversion error?

In spring-boot using namedParameterJdbcTemplate (Oracle db version 12 and odbc8 driver 12.2)

I am getting the following error while executing a SELECT query bound with a parameter larger than 4000 character whereas update queries working fine.

ORA-01460: unimplemented or unreasonable conversion requested

The unit test I am trying to execute;

@Test
public void testSqlSelectQueryLargeStringParameter() {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("param", theLargeStr);

    // @formatter:off
    String sql =
        "SELECT NULL id, NULL text FROM DUAL WHERE 'X' != :param ";
    // @formatter:on

    namedParameterJdbcTemplate.queryForRowSet(sql, params);
}

Is there any way to set this large param via MapSqlParameterSource?

Answer

I am @ahmet-orhan ‘s colleague, we’ve found a solution.

Thanks @kfinity for your suggestion, It is working for insert and update but we are still getting this error when we set clob or blob as “paremeter” in select statements.

If using a driver that supports JDBC4.0, the right solution is create a DefaultLobHandler and set streamAsLob or createTemporaryLob to true.

    MapSqlParameterSource params = new MapSqlParameterSource();
    String myString = "";

    for (int i = 0; i < MAX_CLOB_BLOB_SIZE_IN_SELECT; i++) {
        myString = myString + "1";
    }

    DefaultLobHandler lobHandler = new DefaultLobHandler();
    lobHandler.setStreamAsLob(true);

    params.addValue("param", new SqlLobValue(myString, lobHandler), Types.CLOB);

    // @formatter:off
    String sql =
        "SELECT 1 id  FROM DUAL WHERE :param IS NOT NULL ";
    // @formatter:on

    Integer id = namedParameterJdbcTemplate.queryForObject(sql, params, Integer.class);

We prefer streamAsLob but to be honest we have no idea which one is better.