SQL - How to Compare a Clob

How do I compare two CLOB values in Oracle

The format is this:

dbms_lob.compare(  
lob_1    IN BLOB,  
lob_2    IN BLOB,  
amount   IN INTEGER := 18446744073709551615,  
offset_1 IN INTEGER := 1,  
offset_2 IN INTEGER := 1)  
RETURN INTEGER; 

---

If dbms_lob.compare(lob1, lob2) = 0, they are identical.

Here's an example query based on your example:

Select key, glob_value  
From source_table Left Join target_table  
  On source_table.key = target_table.key  
Where target_table.glob_value is Null  
  Or dbms_lob.compare(source_table.glob_value, target_table.glob_value) <> 0

How to compare a local CLOB column against a CLOB column in a remote database instance

You can use an Oracle global temporary table to pull the CLOBs over to your local instance temporarily. You can then use the DBMS_LOB.COMPARE function to compare the CLOB columns.

If this query returns any rows, the CLOBs are different (more or less characters, newlines, etc) or one of the rows exists in only one of the instances.

--Create temporary table to store the text in
CREATE GLOBAL TEMPORARY TABLE X.TEMP_TEXT_TABLE
ON COMMIT DELETE ROWS
AS
   SELECT * FROM X.TEXT_TABLE@REMOTE_DB;

--Use this statement if you need to refresh the TEMP_TEXT_TABLE table
INSERT INTO X.TEMP_TEXT_TABLE
SELECT * FROM X.TEXT_TABLE@REMOTE_DB;

--Do the comparision   
SELECT DISTINCT
       TARGET.NAME TARGET_NAME
      ,SOURCE.NAME SOURCE_NAME
      ,DBMS_LOB.COMPARE (TARGET.TEXT, SOURCE.TEXT) AS COMPARISON
  FROM    (SELECT ID, NAME, TEXT FROM X.TEMP_TEXT_TABLE) TARGET
       FULL OUTER JOIN
          (SELECT ID, NAME, TEXT FROM X.TEXT_TABLE) SOURCE
       ON TARGET.ID = SOURCE.ID
 WHERE DBMS_LOB.COMPARE (TARGET.TEXT, SOURCE.TEXT) <> 0
    OR  DBMS_LOB.COMPARE (TARGETTEXT, SOURCE.TEXT) IS NULL;

Querying oracle clob column

Yes, it's not allowed (this restriction does not affect CLOBs comparison in PL/SQL)
to use comparison operators like =, !=, <> and so on in SQL statements, when trying
to compare two CLOB columns or CLOB column and a character literal, like you do. To be
able to do such comparison in SQL statements, dbms_lob.compare() function can be used.

  select * 
    from aTable 
   where dbms_lob.compare(aClobColumn, 'value') = 0

In the above query, the 'value' literal will be implicitly converted to the CLOB data type.
To avoid implicit conversion, the 'value' literal can be explicitly converted to the CLOB
data type using TO_CLOB() function and then pass in to the compare() function:

  select * 
    from aTable 
   where dbms_lob.compare(aClobColumn, to_clob('value')) = 0

Oracle SQL: Compare 2 CLOBS from different tables (over 4000 bytes)

Put the comparison into the ON clause of the LEFT JOIN:

SELECT rm.order_no,
       r.request_id,
       rr.request_id as INLR,
       r.request
FROM   inl_request_message rm
       INNER JOIN inl_mml_requests r
       ON (rm.request_id = r.request_id)
       LEFT JOIN inlr_mml_requests rr
       ON (   rr.request_id = r.request_id
          AND dbms_lob.compare(rr.request, rm.request) <> 0 )
WHERE  rr.request_id is null

If you put it in the WHERE clause then you require both values in the comparison to be non-NULL and effectively convert the LEFT JOIN to an INNER JOIN.

---

Related Topics