Oracle SQL:比较来自不同表的2个clob(超过4000字节)

Question

我正试图编写一个查询来比较Oracle SQL中来自不同表的2个CLOB数据类型，以验证它们是否相同。

我试过这个例子:

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

这是我的实现:

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

我不确定我做错了什么，如果我把dbms_lob部分从查询中取出，查询就会按预期运行，否则将不会运行。

CLOB值为XML

Answer 1

将比较放入LEFT JOIN的ON子句中:

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

如果你把它放在WHERE子句中，那么你需要比较中的两个值都是非NULL，并有效地将LEFT JOIN转换为INNER JOIN。

Answer 2

您应该这样管理空值:

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;

请检查此声明"和inlr_mml_requests。Request_id为null"似乎只考虑空键值。

如果有用的话，我给你带来这个例子:

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>                         

谢谢