Skip to Main Content
  • Questions

  • dbms_metadata - getting inconsistent SXML (ordering)

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kim.

Asked: August 22, 2017 - 8:11 am UTC

Last updated: August 24, 2017 - 12:49 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

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

and Chris said...

I'm not aware of a way to force the ordering with dbms_metadata.

But even if there is, comparing schemas this way seems like a lot of work. I'd rather do this with a tool.

Fortunately we have a free one you can use to do just this ;)

SQL Developer has a Database Diff option you can use to find the differences and get the scripts:

http://www.thatjeffsmith.com/archive/2012/09/sql-developer-database-diff-compare-objects-from-multiple-schemas/

Or if you want to model your DB more comprehensively, get Oracle SQL Developer Data Modeler. This also allows you to compare designs and schemas:

http://www.oracle.com/technetwork/developer-tools/datamodeler/downloads/index.html

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Comparing is actually easy

Kim, August 22, 2017 - 11:04 am UTC

Hi Chris

Thank You for the suggestions.

However, we use this to notify our developers by email, when something they are responsible for is out of sync.

Comparing our schema, which has something like 10.000 objects with a GUI tool is not really a solid approach, and then trying to identify who should be notified of what.

However, comparing the objects programatically is actually very easy, since dbms_metadata has a procedure for this.

transform_handle :=
dbms_metadata.add_transform(openw_handle,'ALTERXML');

Before this step, You add the two SXML documents, and then does this transformation. The result is an XML document that can be transformed into plain DDL.

So with not so much code, this works perfectly, except for the issue described above.
Chris Saxon
August 22, 2017 - 12:56 pm UTC

Hmmm. I feel like something's not right with the process.

Won't the prod and test databases often be out-of-sync? And how do you know which developers to notify when they are? As you develop the app you'll get new objects added. Who's managing the notification list?

Details

A reader, August 23, 2017 - 6:30 am UTC

Hi Chris

The developers are getting notification of new differences made the last two days.
And once in a while, they get a total list.
We could make the total list a webpage, but have not gone there yet.

The schema is very old, and it is shared between a number of departments, who do not have a common development strategy.

We can see, that some changes are not made to every environment, and some changes never make it to production.
This comparison enabless us to get in control again.

Note, that it is not feasible to clone production, as the non-prod environments are connected to other databases etc., and share common data only found in that environment.

Br,
Kim

Currently I am trying this...

A reader, August 23, 2017 - 8:01 am UTC

select xmltransform ( xmltype ( ddl_xml) , xmltype.createxml('<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl=" http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<xsl:apply-templates/>
</xsl:template>
<xsl:template match="*">
<xsl:copy>
<!-- Sort the attributes by name. -->
<xsl:for-each select="@*">
<xsl:sort select="name( . )"/>
<xsl:copy/>
</xsl:for-each>
<xsl:apply-templates/>
</xsl:copy>
</xsl:template>
<xsl:template match="text()|comment()|processing-instruction()">
<xsl:copy/>
</xsl:template>
</xsl:stylesheet>'))
from ......

However, since the XML that needs to be sorted by the value between the NAME tags, it does not work.

Here is an example of the XML:

<?xml version="1.0" encoding="UTF-8"?>
<TABLE xmlns=" http://xmlns.oracle.com/ku" version="1.0">
....
<RELATIONAL_TABLE>
.....
<CHECK_CONSTRAINT_LIST>
<CHECK_CONSTRAINT_LIST_ITEM>
<NAME>CONSTRAINT_X</NAME> <--- This needs to be last
<CONDITION>...</CONDITION>
</CHECK_CONSTRAINT_LIST_ITEM>
<CHECK_CONSTRAINT_LIST_ITEM>
<NAME>CONSTRAINT_A</NAME> <--- This needs to be first
....

So close....

A reader, August 23, 2017 - 2:17 pm UTC

Hi Chris,

I am so close to a nice little solution, however, I am struggling with the XML namespace that dbms_metadata introduces. If I leave that out, this little piece of code wotks:

select xmltransform (
xmltype (
q'|<TABLE xmlns=" http://xmlns.oracle.com/ku" version="1.0">
<SCHEMA>SCOTT</SCHEMA>
<NAME>EMP</NAME>
<RELATIONAL_TABLE>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>LAST_NAME</NAME>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>FIRST_NAME</NAME>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>DEPT_ID</NAME>
</COL_LIST_ITEM>
</COL_LIST>
</RELATIONAL_TABLE>
</TABLE>|')
, xmltype.createxml(
q'|<xsl:stylesheet version="1.0" xmlns:xsl=" http://www.w3.org/1999/XSL/Transform" >
<xsl:template match="/">
<xsl:for-each select="TABLE/RELATIONAL_TABLE/COL_LIST/COL_LIST_ITEM">
<xsl:sort select="NAME"/>
<xsl:copy-of select="."/>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>|')) transformed_xml
from dual;

Solution - xml templates

A reader, August 24, 2017 - 10:26 am UTC

Hi Chris,

I think that I solved the problem, using XML templates:

select xmltransform (
xmltype (
q'|<TABLE xmlns=" http://xmlns.oracle.com/ku" version="1.0">
<SCHEMA>SCOTT</SCHEMA>
<NAME>EMP</NAME>
<RELATIONAL_TABLE>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>XXX</NAME>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>MMM</NAME>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>AAA</NAME>
</COL_LIST_ITEM>
</COL_LIST>
</RELATIONAL_TABLE>
</TABLE>|')
, xmltype.createxml(
q'|<xsl:stylesheet version="1.0" xmlns:xsl=" http://www.w3.org/1999/XSL/Transform" xmlns:x=" http://xmlns.oracle.com/ku" >
<xsl:template match="/">
<xsl:copy>
<xsl:apply-templates/>
</xsl:copy>
</xsl:template>
<xsl:template match="node()|@*">
<xsl:copy>
<xsl:apply-templates select="node()|@*"/>
</xsl:copy>
</xsl:template>
<xsl:template match="x:COL_LIST">
<xsl:for-each select="x:COL_LIST_ITEM">
<xsl:sort select="x:NAME"/>
<xsl:copy-of select="."/>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>|')) transformed_xml
from dual;

Chris Saxon
August 24, 2017 - 10:33 am UTC

Glad you sorted it, thanks for sharing.

Final code

A reader, August 24, 2017 - 12:36 pm UTC

Hi Chris

If You find it usefull, then this piece of code is (hopefully) complete, in respect to sorting the relevant pieces of the SXML that dbms_metadata produces:

select xmltransform (
xmltype ( <ddl_xml> )
, xmltype.createxml(
q'|<xsl:stylesheet version="1.0" xmlns:xsl=" http://www.w3.org/1999/XSL/Transform" xmlns:x=" http://xmlns.oracle.com/ku" >
<xsl:template match="/">
<xsl:copy>
<xsl:apply-templates/>
</xsl:copy>
</xsl:template>
<xsl:template match="node()|@*">
<xsl:copy>
<xsl:apply-templates select="node()|@*"/>
</xsl:copy>
</xsl:template>
<xsl:template match="x:CHECK_CONSTRAINT_LIST|x:FOREIGN_KEY_CONSTRAINT_LIST">
<xsl:for-each select="x:CHECK_CONSTRAINT_LIST_ITEM|x:FOREIGN_KEY_CONSTRAINT_LIST_ITEM">
<xsl:sort select="x:NAME"/>
<xsl:copy-of select="."/>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>|')) transformed_xml
from ....

Here, I do not sort the column names, but only the constraints and foreign key constraints.

Best regards & thank You for a great service,
Kim
Chris Saxon
August 24, 2017 - 12:49 pm UTC

Thanks for sharing, I'm sure someone this will help someone :)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library