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
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
Answered By – MT0
Answer Checked By – Robin (BugsFixing Admin)