9

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?

skaffman
  • 398,947
  • 96
  • 818
  • 769
Petteri H
  • 11,779
  • 12
  • 64
  • 94
  • 5
    Do you actually need a CLOB ? If so, you could have a separate column that takes the first 100 characters of the CLOB, and order by that. EG ORDER BY cast(substr(clob_col,1,100) as varchar2(100)) – Gary Myers Sep 13 '10 at 23:22
  • CLOB is what we have at the moment because of the data size. We might have to introduce a view with an extra column for sorting and update Hibernate mappings.Just have to figure out how to do it nicely for all supported DBs. – Petteri H Sep 14 '10 at 08:24
  • I used distinct() because I discovered in another query that Hibernate has a bug with "group by" when using a simple query like mine. But if you've got a type with a CLOB column, distinct doesn't work because of an issue with Oracle. Distinct uses a UNION to get its results, and unions apparently don't work on types that have CLOBs. I'll be using Apache Commons' SetUniqueList to pare down the list in Java. (My result set is relatively small.) https://forum.hibernate.org/viewtopic.php?f=1&t=998284 – Marvo Jun 08 '15 at 23:34

1 Answers1

11

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.
JoshL
  • 10,737
  • 11
  • 55
  • 61
  • dbms_lob.substr(?,?,?) sintax: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#i999349 - second argument is AMOUNT, third - OFFSET starting from 1 – Maxple Jun 15 '21 at 13:14