Geek Answers Handbook

Oracle CLOB and JPA / Hibernate ORDER BY?

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 is that Oracle does not support ORDER BY with CLOB columns.

Is there any jpql for this?

+6
oracle hibernate jpa jpql ora-00932
source share
1 answer

You will need to convert the CLOB to Varchar to do the sorting. Unfortunately, Varchar columns are limited to 4,000 characters in Oracle. If sorting by the first 4000 characters is reasonable, here's an example of SQLPlus 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.
+4
source share

More articles:


All Articles