Oracle CLOB and JPA/Hibernate ORDER BY?

Question

I have a JPQL query that works fine with MySQL and SQL Server. But with Oracle it fails with

ORA-00932: inconsistent datatypes: expected - got CLOB

The reason seems to be that Oracle does not support ORDER BY with CLOB columns.

Is there any JPQL work around for this?

Answer 1

You'll need to convert the CLOB into a Varchar in order to do the sort. Unfortunately Varchar columns are limited to 4000 characters in Oracle. If sorting by the first 4000 characters is reasonable, here's a SQLPlus example using DBMS_LOB.SUBSTR:

SQL> create table mytable (testid int, sometext clob);

Table created.

SQL> insert into mytable values (1, rpad('z',4000,'z'));

1 row created.

SQL> update mytable set sometext = sometext || sometext || sometext;

1 row updated.

SQL> select length(sometext) from mytable;

LENGTH(SOMETEXT)
----------------
           12000

SQL> select testid from mytable
  2  order by dbms_lob.substr(sometext, 0, 4000);

    TESTID
----------
         1

SQL> drop table mytable;

Table dropped.