How do I compare two strings/CLOB in Oracle 9.2?


How do I compare two strings/CLOBs in Oracle 9.2?
I have a new CLOB value and I need to compare it with another CLOB value in a table.  If the new and old values are different, I need to update the table.  Otherwise, I move on to the next record.

Is there a PL/SQL function that can compare two CLOBs with each other?

Thanks,
Troy
tdsimpsoAsked:
Who is Participating?
 
paquicubaConnect With a Mentor Commented:
SQL> SELECT * FROM CLOB1
  2  /

COL1
--------------------------------------------------------------------------------
ABCDEFGHIJKL
ABCDEFGHIJK
LMNOPQRST

Elapsed: 00:00:00.00
SQL> SELECT * FROM CLOB2
  2  /

COL1
--------------------------------------------------------------------------------
ABCDEFGHIJK
LMNOPQRST
LM

Elapsed: 00:00:00.00
SQL> SELECT * FROM(
  2  SELECT DECODE(DBMS_LOB.COMPARE(A.COL1,B.COL1),0,A.COL1,NULL) COL1 FROM CLOB1 A, CLOB2 B)
  3  WHERE COL1 IS NOT NULL
  4  /

COL1
--------------------------------------------------------------------------------
ABCDEFGHIJK
LMNOPQRST

Elapsed: 00:00:00.00
0
 
tdsimpsoAuthor Commented:
Would it be better if I convert the two CLOB's into an Integer Value and compare the values?

Thanks,
Troy
0
 
paquicubaCommented:
 1  SELECT A.COL1
  2  FROM CLOB1 A, CLOB2 B
  3* WHERE DBMS_LOB.COMPARE(A.COL1,B.COL1)= 0
SQL> /

COL1
--------------------------------------------------------------------------------
ABCDEFGHIJK
LMNOPQRST
0
All Courses

From novice to tech pro — start learning today.