Hi Tom
I am trying to use dbms_metadata to retrieve a SXML document per object in my production database, and compare it to the same document from my test database, in order to compare the two environments.
Initially, I calculate a HASH value for the clob, to see if the documents are the same.
This works great, and I can easily get a DDL to "fixup" either of the environments, using the two SXML documents.
But, I have found that the order of constraints are not consistent, and this messes up my comparison of the HASH values.
I have been trying, using XMLTABLE() i sql, to extract the SXML as a table, but this does not seem like a solid approach.
Is there any way of making dbms_metadata list the constraints in order, or is there a way of ordering the XML easily ?
Here is a function that I use, to extract the SXML for an object:
function get_object_sxml
( object_owner in varchar2
, object_type in varchar2
, object_name in varchar2
, network_link in varchar2 default NULL
)
return clob
is
open_handle number;
transform_handle number;
doc clob;
begin
open_handle := dbms_metadata.open( object_type => object_type, network_link => network_link );
--
if ( object_owner is not null and object_owner != user )
then
dbms_metadata.set_filter(open_handle,'SCHEMA', object_owner);
end if;
dbms_metadata.set_filter(open_handle,'NAME', object_name );
--
-- Use the 'SXML' transform to convert XML to SXML
transform_handle := dbms_metadata.add_transform(open_handle,'SXML');
--
-- Use this transform parameter to suppress physical properties
dbms_metadata.set_transform_param(transform_handle,'PHYSICAL_PROPERTIES', false);
doc := dbms_metadata.fetch_clob(open_handle);
dbms_metadata.close(open_handle);
return doc;
exception
when others then
dbms_output.put_line('Error while fetching DDL for: '||object_type||' '||object_owner||'.'||object_name||'@'||network_link);
dbms_output.put_line(sqlerrm);
return null; --empty_clob();
end get_object_sxml;
Best regards,
Kim