Oracle CLOB 和 JPA/Hibernate ORDER BY？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3699794/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle CLOB and JPA/Hibernate ORDER BY?
提问by Petteri Hietavirta
I have a JPQL query that works fine with MySQL and SQL Server. But with Oracle it fails with
我有一个适用于 MySQL 和 SQL Server 的 JPQL 查询。但是使用 Oracle 它失败了
ORA-00932: inconsistent datatypes: expected - got CLOB
The reason seems to be that Oracle does not support ORDER BY with CLOB columns.
原因似乎是 Oracle 不支持带有 CLOB 列的 ORDER BY。
Is there any JPQL work around for this?
有没有 JPQL 解决这个问题？
采纳答案by JoshL
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:
您需要将 CLOB 转换为 Varchar 才能进行排序。不幸的是，Varchar 列在 Oracle 中被限制为 4000 个字符。如果按前 4000 个字符排序是合理的，这里是一个使用 DBMS_LOB.SUBSTR 的 SQLPlus 示例：
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.
