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

Question

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?

score 1 · Answer 1 · answered Dec 06 '19 at 14:29

1

This comment points out that ORA-01460 in JDBC queries is the same as "ORA-01704: string literal too long". (You can't have string literals longer than 4000 characters.) Maybe try this solution?

params.addValue("param", theLargeStr, Types.CLOB);

Although also != won't work for clob comparison, so you'll also need to change your query to

SELECT NULL id, NULL text FROM DUAL WHERE dbms_lob.compare('X',:param) != 0

answered Dec 06 '19 at 14:29

kfinity

8,581
1
13
20

Appreciated for answer, I had tried Types.CLOB before but result is still same. – Ahmet Orhan Dec 09 '19 at 07:00

score 1 · Accepted Answer · edited Jan 14 '20 at 08:31

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.

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

2 Answers2