Setting a Clob value in a native query

Question

Oracle DB.

Spring JPA using Hibernate.

I am having difficulty inserting a Clob value into a native sql query. The code calling the query is as follows:

@SuppressWarnings("unchecked")
public List<Object[]> findQueryColumnsByNativeQuery(String queryString, Map<String, Object> namedParameters)
{
    List<Object[]> result = null;

    final Query query = em.createNativeQuery(queryString);

    if (namedParameters != null)
    {
        Set<String> keys = namedParameters.keySet();
        for (String key : keys)
        {
            final Object value = namedParameters.get(key);

            query.setParameter(key, value);

        }
    }
    query.setHint(QueryHints.HINT_READONLY, Boolean.TRUE);
    result = query.getResultList();
    return result;
}

The query string is of the format

SELECT  COUNT  (  DISTINCT  ( <column>  )  )   FROM  <Table> c  where (exact ( <column> ,  (:clobValue),  null  )  =  1 )

where "(exact ( , (:clobValue), null ) = 1 )" is a function and "clobValue" is a Clob.

I can adjust the query to work as follows:

SELECT  COUNT  (  DISTINCT  ( <column>  )  )   FROM  <Table> c  where (exact ( <column> ,  to_clob((:stringValue)),  null  )  =  1 )

where "stringValue" is a String but obviously this only works up to the max sql string size (4000) and I need to pass in much more than that.

1. I have tried to pass the Clob value as a java.sql.Clob using the method

   final Clob clobValue = org.hibernate.engine.jdbc.ClobProxy.generateProxy(stringValue);

This results in a
java.io.NotSerializableException: org.hibernate.engine.jdbc.ClobProxy

2. I have tried to Serialize the Clob using

   final Clob clob = org.hibernate.engine.jdbc.ClobProxy.generateProxy(stringValue);
   final Clob clobValue = SerializableClobProxy.generateProxy(clob);

But this appears to provide the wrong type of argument to the "exact" function resulting in
(org.hibernate.engine.jdbc.spi.SqlExceptionHelper:144) - SQL Error: 29900, SQLState: 99999 (org.hibernate.engine.jdbc.spi.SqlExceptionHelper:146) - ORA-29900: operator binding does not exist ORA-06553: PLS-306: wrong number or types of arguments in call to 'EXACT'

3. After reading some post about using Clobs with entities I have tried passing in a byte[] but this also provides the wrong argument type
   (org.hibernate.engine.jdbc.spi.SqlExceptionHelper:144) - SQL Error: 29900, SQLState: 99999 (org.hibernate.engine.jdbc.spi.SqlExceptionHelper:146) - ORA-29900: operator binding does not exist ORA-06553: PLS-306: wrong number or types of arguments in call to 'EXACT'

4. I can also just pass in the value as a String as long as it doesn't break the max string value

I have seen a post (Using function in where clause with clob parameter) which seems to suggest that the only way is to use "plain old JDBC". This is not an option. I am up against a hard deadline so any help is very welcome.

Answer 1

THE ANSWER: Thank you both for your answers. I should have updated this when i solved the issue some time ago. In the end I used JDBC and the problem disappeared in a puff of smoke!