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

Issue

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

Solution

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.

Answered By – MT0

Answer Checked By – Robin (BugsFixing Admin)

Leave a Reply

Your email address will not be published.