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

Question

I am trying to write a query to compare 2 CLOB data types in Oracle SQL to each other from different tables to verify that they are the same.

I have tried this 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

This is my implementation:

select inl_request_message.order_no,inl_mml_requests.request_id,inlr_mml_requests.request_id 
as INLR, inl_mml_requests.request from inl_request_message,inl_mml_requests left join inlr_mml_requests on 
inlr_mml_requests.request_id = inl_mml_requests.request_id
where inl_request_message.request_id = inl_mml_requests.request_id and inlr_mml_requests.request_id is null 
and dbms_lob.compare(inlr_mml_requests.request, inl_request_message.request) <> 0

I am unsure what I am doing wrong as if I take the dbms_lob section out of the query the query runs just as intended, otherwise will not run.

CLOB value is XML

Answer 1

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.