title: How to sort by CLOB column with Hibernate / DB2
body: <p>I'm using Hibernate 4 and DB2 10.1. When I sort an entity (<code>criteria.addOrder(...)</code>) by a property that maps to a CLOB column, I get the following error:</p>
  
  <pre><code>[...] could not extract ResultSet:.org.hibernate.exception.SQLGrammarException: could not extract ResultSet
      at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)
      at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
      at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:88)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:2062)
      at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1859)
      at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
      at org.hibernate.loader.Loader.doQuery(Loader.java:906)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:348)
      at org.hibernate.loader.Loader.doList(Loader.java:2550)
      at org.hibernate.loader.Loader.doList(Loader.java:2536)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2366)
      at org.hibernate.loader.Loader.list(Loader.java:2361)
      at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:124)
      at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1587)
      at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:374)
  [...]
  Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-134, SQLSTATE=42907, SQLERRMC=TEXT7_0_0_, DRIVER=4.15.100
  [...]
  </code></pre>
  
  <p>The reason is that DB2 cannot sort by CLOB columns. So the following statement will fail:</p>
  
  <pre><code>SELECT * FROM MY_TABLE ORDER BY CLOB_COLUMN; 
  </code></pre>
  
  <p>One possible solution to this is to cast a <code>CLOB</code> to a <code>VARCHAR</code>for sorting. So this works fine:</p>
  
  <pre><code>SELECT * FROM MY_TABLE ORDER BY CAST(CLOB_COLUMN AS VARCHAR(1000));
  </code></pre>
  
  <p>How can I get Hibernate to do such a special handling?</p>