Wednesday, January 16, 2008

Comparing CLOBs

What if you have two CLOB columns to compare? A construct like

select someColumn
from tab1
where col1 != col2

will not work.

This is how you will need to compare:

select AUDIT_PAYLOAD_TEXT_DEC, AUDIT_PAYLOAD_TEXT
from audit_payload
where dbms_lob.compare(AUDIT_PAYLOAD_TEXT_DEC, AUDIT_PAYLOAD_TEXT) != 0

The return value is non-zero if the comparison fails.

Translate