查询oracle clob列 [英] Querying oracle clob column

问题描述

我有一个clob栏的桌子。需要执行基于clob列内容的搜索。然而

select * from aTable where aClobColumn ='value';

失败，但

  select * from aTable where aClobColumn like'value'; 
  

似乎工作正常。 oracle如何在clob列上处理过滤。它是否仅支持like子句，而不是=，！=等。是否与其他数据库（如mysql，postgres等）一样？

在实现像hibernate一样的JPA框架中处理？

解决方案

是的，这是不允许的（这个限制不影响 CLOB 在PL / SQL中的比较）
使用比较运算符，如 = ，！= ，<>< code>在SQL语句中尝试使用
来比较两个 CLOB 列或 CLOB 列和字符文字，就像你一样。要成为
能够在SQL语句中进行这种比较，可以使用dbms_lob.compare（）函数。

  select * $ a b $ b from a 
 where dbms_lob.compare（aClobColumn，'value'）= 0 
  

在上面的查询中， 'value'文字将被隐式转换为 CLOB 数据类型。
为了避免隐式转换，可以将'value'文字显式转换为 CLOB
数据类型使用 TO_CLOB（）函数，然后传递给 compare（）函数：

 从aTable 
中选择* 
其中dbms_lob.compare（aClobColumn，to_clob（'value'））= 0 
  




I have a table with a clob column. Searching based on the clob column content needs to be performed. However

select * from aTable where aClobColumn = 'value';

fails but

select * from aTable where aClobColumn like 'value';

seems to workfine. How does oracle handle filtering on a clob column. Does it support only the 'like' clause and not the =,!= etc. Is it the same with other databases like mysql, postgres etc

Also how is this scenario handled in frameworks that implement JPA like hibernate ?

解决方案

Yes, it's not allowed (this restriction does not affect CLOBs comparison in PL/SQL) to use comparison operators like =, !=, <> and so on in SQL statements, when trying to compare two CLOB columns or CLOB column and a character literal, like you do. To be able to do such comparison in SQL statements, dbms_lob.compare() function can be used.

  select * 
    from aTable 
   where dbms_lob.compare(aClobColumn, 'value') = 0

In the above query, the 'value' literal will be implicitly converted to the CLOB data type. To avoid implicit conversion, the 'value' literal can be explicitly converted to the CLOB data type using TO_CLOB() function and then pass in to the compare() function:

  select * 
    from aTable 
   where dbms_lob.compare(aClobColumn, to_clob('value')) = 0

这篇关于查询oracle clob列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！