如何比较 Oracle 中的两个 CLOB 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/85675/
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
How do I compare two CLOB values in Oracle
提问by Zach
I have two tables I would like to complare. One of the columns is type CLOB. I would like to do something like this:
我有两张桌子我想比较。其中一列是类型 CLOB。我想做这样的事情：
select key, clob_value source_table
minus
select key, clob_value target_table
Unfortunately, Oracle can't perform minus operations on clobs. How can I do this?
不幸的是，Oracle 无法对 clob 执行减号操作。我怎样才能做到这一点？
回答by Nick Craver
The format is this:
格式是这样的：
dbms_lob.compare(
lob_1 IN BLOB,
lob_2 IN BLOB,
amount IN INTEGER := 18446744073709551615,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1)
RETURN INTEGER;
If dbms_lob.compare(lob1, lob2) = 0, they are identical.
如果 dbms_lob.compare(lob1, lob2) = 0，则它们是相同的。
Here's an example query based on your example:
这是基于您的示例的示例查询：
Select key, glob_value
From source_table Left Join target_table
On source_table.key = target_table.key
Where target_table.glob_value is Null
Or dbms_lob.compare(source_table.glob_value, target_table.glob_value) <> 0
回答by hamishmcn
Can you access the data via a built in package? If so then perhaps you could write a function that returned a string representation of the data (eg some sort of hash on the data), then you could do
您可以通过内置包访问数据吗？如果是这样，那么也许您可以编写一个函数来返回数据的字符串表示形式（例如，数据上的某种散列），那么您可以这样做
select key, to_hash_str_val(glob_value) from source_table
minus
select key, to_hash_str_val(glob_value) from target_table
