Question:  I need to compare the contents of two CLOB datatypes in a column.  How do you compare CLOB columns?  How I can compare CLOB column in two table?

Answer: Oracle provides the dbms_lob package, specifically the dbms_lob.compare procedure for comparing two CLOBs. The dbms_lob.compare procedure returns a zero of the strings match and a -1 if they do not match. 

Example using dbms_lob.compare in SQL

In this example, we use dbms_lob.compare in SQL to compare if two CLOB columns are matching in two separate tables:  For using dbms_lob.compare in SQL, you can compare two LOB columns within a single table, joining the table against itself, like this example comparing the CLOB column col1 with CLOB column col2. 

select
   a.tab_key
   dbms_lob.compare(nvl(a.col1,'Null'),nvl(b.col2,'Null'))
from
   tab1   a,
   tab1   b
where
   a.tab_key = b.tab_key;

In this example, we might use a cross join to display rows where the CLOB column are duplicates.

BEWARE:  This code below is a cross join because there is no table join criteria in the where clause.  This produces a Cartesian product of all rows in the table, so don't; expect sub-second response time:

select
   cust_details
from
   customer   a,
   customer   b
where
   dbms_lob.compare(a.clob_comments, b.clob_comments) = 0;

Now let's look at examples of using dbms_lob.compare to compare two CLOB columns in PL/SQL.

Example using dbms_lob.compare in PL/SQL

Here is a simple example of using dbms_lob.compare to compare two CLOB datatypes in PL/SQL:

declare
   c1 clob := 'don';
   c2 clob := 'burleson';
begin
      dbms_output.put_line(dbms_lob.compare( c1, c2 ) );
end;
/

In this example, dbms_lob.compare would return -1 because the two CLOB values do not match.

We can also use dbms_lob.compare with SQL inside PL/SQL, something like this:

declare
   c1 clob := 'don';
   c2 clob := 'burleson';
begin
      dbms_output.put_line(dbms_lob.compare( c1, c2 ) );
end;
/

You can also use dbms_lob.compare to compare substrings within a CLOB.  In this example, we add three arguments after the c1 and c2 arguments:

• argument 1 - This is the first CLOB

• argument 2 - This is the second CLOB

• argument 3- This is the number of bytes to compare

• argument 4 - this is the offset into string1 (c1)

• argument 5 - This is the offset into string2 (c1)

Consider this example and note the area of matching text:

declare
    c1 clob := 'don';
   c2 clob := 'mastadon';
begin
      dbms_output.put_line(dbms_lob.compare( c1, c2, 3, 1, 6 ) );
end;
/

Above we return a zero(0) value because we are matching three letters, the first three in string c1 (don) with the three letters in string 3, starting at position 5 (don).

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.