Hi,
I'm not at all acquainted with XML so apologies if the answer is trivial to many.
I want to compare the same object from 2 schemas to check for differences. I thought of using DBMS_METADATA along with XML, getting examples from the documentation.
I'm using a PL/SQL block because I want the comparison to ignore the schemas. My DB version is 11.2.0.3 standard edition (therefore I cannot use DBMS_METADATA_DIFF).
I've started my process with TYPES.
This is what I've basically done so far:
-- Create the same TYPE in 2 different schemas for comparison
create user schema1 identified by schema1;
create user schema2 identified by schema2;
create type schema1.my_type as object(id number);
create type schema2.my_type as object(id number);
These 2 types are identical, so there should be no difference found in comparing them:
-- Use a PL/SQL block to do the comparison
DECLARE
h1 NUMBER;
th1 NUMBER;
doc1 CLOB;
h2 NUMBER;
th2 NUMBER;
doc2 CLOB;
diff xmltype;
val varchar2(4000);
BEGIN
-- Specify the object type.
h1 := DBMS_METADATA.OPEN('TYPE');
h2 := DBMS_METADATA.OPEN('TYPE');
-- Use filters to specify the particular object desired.
DBMS_METADATA.SET_FILTER(h1,'SCHEMA','SCHEMA1');
DBMS_METADATA.SET_FILTER(h1,'NAME','MY_TYPE');
DBMS_METADATA.SET_FILTER(h2,'SCHEMA','SCHEMA2');
DBMS_METADATA.SET_FILTER(h2,'NAME','MY_TYPE');
-- Remap the schema name to NULL so that types can be compared irrespective of their owner
th1 := DBMS_METADATA.ADD_TRANSFORM(h1,'MODIFY');
DBMS_METADATA.SET_REMAP_PARAM(th1,'REMAP_SCHEMA','SCHEMA1',NULL);
th2 := DBMS_METADATA.ADD_TRANSFORM(h2,'MODIFY');
DBMS_METADATA.SET_REMAP_PARAM(th2,'REMAP_SCHEMA','SCHEMA2',NULL);
-- Request that the metadata be transformed into creation SXML
th1 := DBMS_METADATA.ADD_TRANSFORM(h1,'SXML');
th2 := DBMS_METADATA.ADD_TRANSFORM(h2,'SXML');
-- Fetch the object.
doc1 := DBMS_METADATA.FETCH_CLOB(h1);
dbms_output.put_line(doc1);
doc2 := DBMS_METADATA.FETCH_CLOB(h2);
dbms_output.put_line(doc2);
-- Use dbms_lob.compare
dbms_output.put_line('DBMS_LOB.COMPARE result : ' || dbms_lob.compare(doc1, doc2));
-- Use XMLDIFF
select xmldiff(xmltype(doc1), xmltype(doc2)) into diff from dual;
-- Check if the result is NULL
if diff is null
then dbms_output.put_line('XMLDIFF found no diff');
else dbms_output.put_line('XMLDIFF found some diff');
end if;
-- Display the diff
select '-->' || xmlcast(diff as varchar2(4000)) || '<--' into val from dual;
dbms_output.put_line(val);
END;
The result:
<TYPE_SPEC xmlns="http://xmlns.oracle.com/ku" version="1.0">
<SCHEMA></SCHEMA>
<NAME>MY_TYPE</NAME>
<OBJECT>
<ATTRIBUTE_LIST>
<ATTRIBUTE_LIST_ITEM>
<NAME>ID</NAME>
<DATATYPE>NUMBER</DATATYPE>
</ATTRIBUTE_LIST_ITEM>
</ATTRIBUTE_LIST>
</OBJECT>
</TYPE_SPEC>
<TYPE_SPEC xmlns="http://xmlns.oracle.com/ku" version="1.0">
<SCHEMA></SCHEMA>
<NAME>MY_TYPE</NAME>
<OBJECT>
<ATTRIBUTE_LIST>
<ATTRIBUTE_LIST_ITEM>
<NAME>ID</NAME>
<DATATYPE>NUMBER</DATATYPE>
</ATTRIBUTE_LIST_ITEM>
</ATTRIBUTE_LIST>
</OBJECT>
</TYPE_SPEC>
DBMS_LOB.COMPARE result : 0
XMLDIFF found some diff
--><--
PL/SQL procedure successfully completed
My problem as you see is with the XMLDIFF function. I thought it would return NULL for identical XML documents (though the documentation never states so!), but it seems the return value is not null, whereas XMLCAST shows nothing and DBMS_LOB.compare finds no difference.
So, how do I test for no difference using XMLDIFF?
Thanks