I was triggered by a presentation of Dominic Delmolino during Hotsos 2008. He demonstrated a cool way of applying DDL database change management while making use of the Oracle database audit functionality. I combined the idea with Laurent Schneiders “out of the XML box thinking“, like for instance, his alternative use of the XMLTABLE syntax creating a pivot table. So why not do it the same way, solving “relational” problems, via XML functionality.
As said, the idea came up during Dominic Delmolino Hotsos presentation. Since Oracle 11g, the XMLDB functionality has a build in function called XMLDIFF. In Oracle 10 only a Java and C API exists, but probably could be used in the same way as described here. The XMLDIFF function compares two XML documents and captures the differences in XML conforming to an Xdiff schema. The diff document is returned as an XMLType document. Playing with Dominico’s idea, I thought, why not try to use the DBMS_METADATA.GET_XML function, transform this to an XMLType and then process it via XMLDIFF, and see if this can be useful for database change management purposes. So I tried.
So here it goes.
An example script can be downloaded here