Oracle CLOB 和 JPA/Hibernate ORDER BY？

Question

我有一个在MySQL和SQL Server中运行良好的JPQL查询。但是在Oracle中它失败了。

ORA-00932: inconsistent datatypes: expected - got CLOB

似乎是因为Oracle不支持使用CLOB列进行ORDER BY排序。是否有任何JPQL的解决方法？

- Petteri H

---

你是否真的需要一个CLOB？如果是这样，你可以有一个单独的列来存储CLOB的前100个字符，并根据该列进行排序。例如：ORDER BY cast(substr(clob_col,1,100) as varchar2(100))。 - Gary Myers

目前由于数据大小，我们使用CLOB。我们可能需要引入一个带有额外列用于排序的视图，并更新Hibernate映射。我们只需要找出如何在所有支持的数据库上优雅地实现它。 - Petteri H

我使用了distinct()，因为在另一个查询中发现Hibernate在像我的简单查询一样使用"group by"时存在一个bug。但是如果你有一个带有CLOB列的类型，distinct就不起作用了，因为Oracle存在问题。Distinct使用UNION来获取其结果，而联合似乎不能在具有CLOB的类型上工作。我将在Java中使用Apache Commons的SetUniqueList来缩小列表。(我的结果集相对较小。) https://forum.hibernate.org/viewtopic.php?f=1&t=998284 - Marvo

Answer 1

要进行排序，您需要将CLOB转换为Varchar。不幸的是，Oracle中的Varchar列仅限于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.

- JoshL

---

dbms_lob.substr(?,?,?) 语法：https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#i999349 - 第二个参数是 AMOUNT，第三个参数是从 1 开始的 OFFSET。 - Maxple