title: Setting a Clob value in a native query
body: <p>Oracle DB.</p>
  
  <p>Spring JPA using Hibernate.</p>
  
  <p>I am having difficulty inserting a Clob value into a native sql query.
  The code calling the query is as follows:</p>
  
  <pre><code>@SuppressWarnings("unchecked")
  public List&lt;Object[]&gt; findQueryColumnsByNativeQuery(String queryString, Map&lt;String, Object&gt; namedParameters)
  {
      List&lt;Object[]&gt; result = null;
  
      final Query query = em.createNativeQuery(queryString);
  
      if (namedParameters != null)
      {
          Set&lt;String&gt; 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;
  }
  </code></pre>
  
  <p>The query string is of the format</p>
  
  <pre><code>SELECT  COUNT  (  DISTINCT  ( &lt;column&gt;  )  )   FROM  &lt;Table&gt; c  where (exact ( &lt;column&gt; ,  (:clobValue),  null  )  =  1 )
  </code></pre>
  
  <p>where "(exact (  ,  (:clobValue),  null  )  =  1 )" is a function and "clobValue" is a Clob.</p>
  
  <p>I can adjust the query to  work as follows:</p>
  
  <pre><code>SELECT  COUNT  (  DISTINCT  ( &lt;column&gt;  )  )   FROM  &lt;Table&gt; c  where (exact ( &lt;column&gt; ,  to_clob((:stringValue)),  null  )  =  1 )
  </code></pre>
  
  <p>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.</p>
  
  <ol>
  <li><p>I have tried to pass the Clob value as a java.sql.Clob using the method</p>
  
  <p>final Clob clobValue = org.hibernate.engine.jdbc.ClobProxy.generateProxy(stringValue);  </p></li>
  </ol>
  
  <p>This results in a <br/><em>java.io.NotSerializableException: org.hibernate.engine.jdbc.ClobProxy</em></p>
  
  <ol start="2">
  <li><p>I have tried to Serialize the Clob using</p>
  
  <p>final Clob clob = org.hibernate.engine.jdbc.ClobProxy.generateProxy(stringValue);<br>
  final Clob clobValue = SerializableClobProxy.generateProxy(clob);</p></li>
  </ol>
  
  <p>But this appears to provide the wrong type of argument to the "exact" function resulting in <br/> <em>(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'</em></p>
  
  <ol start="3">
  <li><p>After reading some post about using Clobs with entities I have tried passing in a byte[] but this also provides the wrong argument type <br/> <em>(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'</em></p></li>
  <li><p>I can also just pass in the value as a String as long as it doesn't break the max string value</p></li>
  </ol>
  
  <p>I have seen a post (<a href="https://stackoverflow.com/questions/20100184/using-function-in-where-clause-with-clob-parameter">Using function in where clause with clob parameter</a>) 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.</p>