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

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

CodePudding user response：

You should manage the null values thus:

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(nvl(inlr_mml_requests.request, ' '), nvl(inl_request_message.request, ' ')) <> 0;

Please you check also this statement "and inlr_mml_requests.request_id is null" that seems consider only null keys values.

if it can be useful I bring you this example:

create table allnet.mytable
(
  field1    clob,
  field2    clob
);

insert into allnet.mytable values ('<XML><ID><NAME>one field></NAME></ID></XML>','<XML><ID><NAME>one field></NAME></ID></XML>');
insert into allnet.mytable values ('<XML><ID><NAME>one field></NAME></ID></XML>','<XML><ID><NAME>two field></NAME></ID></XML>');
insert into allnet.mytable values ('<XML><ID><NAME>one field></NAME></ID></XML>',null);

select * from allnet.mytable 
 where dbms_lob.compare(field1, field2) = 0

FIELD1                                                                           FIELD2                                                                          
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
<XML><ID><NAME>one field></NAME></ID></XML>                                      <XML><ID><NAME>one field></NAME></ID></XML>   

select * from allnet.mytable 
 where dbms_lob.compare(nvl(field1,' '), nvl(field2,' ')) <> 0
 
FIELD1                                                                           FIELD2                                                                          
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
<XML><ID><NAME>one field></NAME></ID></XML>                                      <XML><ID><NAME>two field></NAME></ID></XML>                                     
<XML><ID><NAME>one field></NAME></ID></XML>

Thank you

CodePudding user response：

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.