8

I have two tables I would like to complare. One of the columns is type CLOB. I would like to do something like this:

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?

wolφi
  • 8,091
  • 2
  • 35
  • 64
Zach
  • 163
  • 3
  • 3
  • 5

2 Answers2

15

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.

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
Nick Craver
  • 623,446
  • 136
  • 1,297
  • 1,155
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
hamishmcn
  • 7,843
  • 10
  • 41
  • 46
  • Yes, we've used that in production to compare a before / after pair of tables. We ended up using the built in `dbms_crypto.hash`... See [here](https://stackoverflow.com/questions/23237326/how-to-use-ora-hash-on-a-column-of-datatype-xmltype) – wolφi Jul 04 '18 at 14:57