SQL - How do you compare a CLOB

Question

in a DB2 trigger, I need to compare the value of a CLOB field. Something like:

IF OLD_ROW.CLOB_FIELD != UPDATED_ROW.CLOB_FIELD 

but "!=" does not work for comparing CLOBs.

What is the way to compare it?

Edited to add:

My trigger needs to do some action if the Clob field was changed during an update. This is the reason I need to compare the 2 CLOBs in the trigger code. I'm looking for some detailed information on how this can be done

Answer 1

In Oracle 10g you can use DBMS_LOB.compare() API.

Example:

select * from table t where dbms_lob.compare(t.clob1, t.clob2) != 0

Full API:

DBMS_LOB.COMPARE (
   lob_1            IN BLOB,
   lob_2            IN BLOB,
   amount           IN INTEGER := 4294967295,
   offset_1         IN INTEGER := 1,
   offset_2         IN INTEGER := 1)
  RETURN INTEGER;

DBMS_LOB.COMPARE (
   lob_1            IN CLOB  CHARACTER SET ANY_CS,
   lob_2            IN CLOB  CHARACTER SET lob_1%CHARSET,
   amount           IN INTEGER := 4294967295,
   offset_1         IN INTEGER := 1,
   offset_2         IN INTEGER := 1)
  RETURN INTEGER; 

DBMS_LOB.COMPARE (
   lob_1            IN BFILE,
   lob_2            IN BFILE,
   amount           IN INTEGER,
   offset_1         IN INTEGER := 1,
   offset_2         IN INTEGER := 1)
  RETURN INTEGER;

Answer 2

Calculate the md5 (or other) hash of the clobs and then compare these. Initial calculation will be slow but comparison is fast and easy. This could be a good method if the bulk of your data doesn't change very often.

One way to calculate md5 is through a java statement in your trigger. Save these in the same table (if possible) or build a simple auxiliary table.