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_lobpare（）函数。
select * $ a b $ b from a where dbms_lobpare（aClobColumn，'value'）= 0在上面的查询中， 'value'文字将被隐式转换为 CLOB 数据类型。 为了避免隐式转换，可以将'value'文字显式转换为 CLOB 数据类型使用 TO_CLOB（）函数，然后传递给 compare（）函数：
从aTable 中选择* 其中dbms_lobpare（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_lobpare() function can be used.
select * from aTable where dbms_lobpare(aClobColumn, 'value') = 0In 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_lobpare(aClobColumn, to_clob('value')) = 0
更多推荐
查询oracle clob列
发布评论