CODEWITHSUNDEEP
sqloracle-databasespring-bootjdbc
Ahmet Orhan
Ahmet Orhan

Reputation: 346

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?

Upvotes: 2

Views: 953

Answers (2)

atilla acar
atilla acar

Reputation: 58

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.

Upvotes: 1

kfinity
kfinity

Reputation: 9091

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

Upvotes: 1

Related Questions