如何比较Oracle中的两个CLOB值

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无法对clobs执行减去操作。我怎样才能做到这一点?

2 个解决方案

#1

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

#2

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

#1