In Oracle 8i, you can use the functions in the DBMS_LOB package to do these comparisons.
To compare two clob fields, here's the syntax:
dbms_lob.compare(CLOB1, CLOB2) = 0 (for True) or 1 (for False)
An example
select count(*)
from eo
where dbms_lob.compare(eo.gen_desc, eo.eo_data) = 0;
To compare a clob with a varchar2, try this one:
select count(*)
from eo
where dbms_lob.instr(EO.GEN_DESC, EO.directions) > 0;
It looks for the string that's in directions in gen_desc and returns the starting position of the string if it's found. So anything greater than 0, means that it's in there somewhere.
This is only necessary for Oracle 8i databases. You can use regular comparison functions in Oracle 9
QUERY: Compare two CLOB fields to each other or one CLOB field with a VARCHAR field Print
Modified on: Mon, 9 Apr, 2018 at 3:41 PM
Did you find it helpful? Yes No
Send feedbackSorry we couldn't be helpful. Help us improve this article with your feedback.