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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With