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

I'm afraid your assumptions about CLOBs in Oracle are wrong. In Oracle CLOB locator is something like a file handle. And such handle can be created by the database only. So you can not simply pass CLOB as bind variable. CLOB must be somehow related to database storage, because this it can occupy up to 176TB and something like that can not be held in Java Heap.

So the usual approach is to call either DB functions empty_clob() or dbms_lob.create_temporary (in some form). Then you get a clob from database even if you think it is "IN" parameter. Then you can write as many data as you want into that locator (handle, CLOB) and then you can use this CLOB as a parameter for a query.

If you do not follow this pattern, your code will not work. It does not matter whether you use JPA, SpringBatch or plan JDBC. This constrain is given by the database.

Answer 2

It seems that it's required to set type of parameter explicitly for Hibernate in such cases. The following code worked for me:

Clob clob = entityManager
    .unwrap(Session.class)
    .getLobHelper()
    .createClob(reader, length);
int inserted = entityManager
    .unwrap(org.hibernate.Session.class)
    .createSQLQuery("INSERT INTO EXAMPLE ( UUID,  TYPE,  DATA) VALUES         (:uuid, :type, :data)")
    .setParameter("uuid", java.util.Uuid.randomUUID(), org.hibernate.type.UUIDBinaryType.INSTANCE)
    .setParameter("type", java.util.Uuid.randomUUID(), org.hibernate.type.StringType.INSTANCE)
    .setParameter("data", clob, org.hibernate.type.ClobType.INSTANCE)
    .executeUpdate();

Similar workaround is available for Blob.

Answer 3

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!