SQL - How do you compare a CLOB

4 Answers

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.


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


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

Select distinct on blob

I would recommend adding another column which contains the hash of the blob value. When you store the blob you also calculate the hash value, using SHA256 for example, and store that. When you later want distinct values you just use this new column.

ORA-00932: inconsistent datatypes: expected - got CLOB

You can't put a CLOB in the WHERE clause. From the documentation:

Large objects (LOBs) are not supported in comparison conditions. However, you can use PL/SQL programs for comparisons on CLOB data.

If your values are always less than 4k, you can use:

   SET TEST_Category           = 'just testing'  
 WHERE to_char(TEST_SCRIPT)    = 'something'
   AND ID                      = '10000239';

It is strange to search by a CLOB anyways.. could you not just search by the ID column?

Storing serialized objects in a database is almost always a bad idea, unless you know ahead of time that you don't need to query against them.

How are you serializing the HashMap? There are lots of ways to serialize data and an object like a HashMap. Comparing two maps, especially in serialized form, is not trivial, unless your serialization technique guarantees that two equivalent maps always serialize the same way.

One way you can get around this mess is to use XML serialization for some objects that rarely need to be queried. For example, where I work we have a log table where a certain log message is stored as an XML file in a CLOB field. This xml data represents a serialized Java object. Normally we query against other columns in the record, and only read/write the blob in single atomic steps. However once or twice it was necessary to do some deep inspection of the blob, and using XML allowed this to happen (Oracle supports querying XML in varchar2 or CLOB fields as well as native XML objects). It's a useful technique if used sparingly.

Take a substr of the CLOB and then convert it to a char:

  SET TEST_Category           = 'just testing' 
WHERE to_char(substr(TEST_SCRIPT, 1, 9))    = 'something'
  AND ID                      = '10000239';