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
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.