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
DBMS_METADATA
The dbms_metadata has been greatly improved, also in Oracle 11g. I created 3 tables for this example and to check my findings.
SQL> SELECT * FROM v$version; BANNER ---------------------------------------------------------------------- Oracle DATABASE 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS FOR 32-bit Windows: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production SQL> SET long 10000000 SQL> SET pages 5000 SQL> SET LINES 200 SQL> CREATE TABLE A 2 (id NUMBER(10)); TABLE created. SQL> CREATE TABLE B 2 (id NUMBER(15)); TABLE created. SQL> CREATE TABLE C 2 (id NUMBER(10), 3 extra varchar2(50)); TABLE created. . |
A lot can be achieved via DBMS_METADATA. See, for example, the overview given here on the PSOUG site. The examples are based on the 3 tables as shown here.
SQL> SELECT dbms_metadata.get_ddl('TABLE','A') FROM dual; DBMS_METADATA.GET_DDL('TABLE','A') ------------------------------------------------------------------------- CREATE TABLE "SYSTEM"."A" ( "ID" NUMBER(10,0) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" SQL> SELECT dbms_metadata.get_ddl('TABLE','B') FROM dual; DBMS_METADATA.GET_DDL('TABLE','B') --------------------------------------------------------------------------- CREATE TABLE "SYSTEM"."B" ( "ID" NUMBER(15,0) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" SQL> SELECT dbms_metadata.get_ddl('TABLE','C') FROM dual; DBMS_METADATA.GET_DDL('TABLE','C') ------------------------------------------------------------------------- CREATE TABLE "SYSTEM"."C" ( "ID" NUMBER(10,0), "EXTRA" VARCHAR2(50) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" TABLESPACE "SYSTEM" SQL> SELECT dbms_metadata.compare_alter('TABLE','A','B',USER,USER) FROM dual; DBMS_METADATA.COMPARE_ALTER('TABLE','A','B',USER,USER) ------------------------------------------------------------------------------ ALTER TABLE "SYSTEM"."A" MODIFY ("ID" NUMBER(15,0)) ALTER TABLE "SYSTEM"."A" RENAME TO "B" SQL> SELECT dbms_metadata.compare_alter('TABLE','A','C',USER,USER) FROM dual; DBMS_METADATA.COMPARE_ALTER('TABLE','A','C',USER,USER) ------------------------------------------------------------------------------- ALTER TABLE "SYSTEM"."A" ADD ("EXTRA" VARCHAR2(50)) ALTER TABLE "SYSTEM"."A" RENAME TO "C" SQL> SELECT dbms_metadata.compare_alter('TABLE','B','C',USER,USER) FROM dual; DBMS_METADATA.COMPARE_ALTER('TABLE','B','C',USER,USER) -------------------------------------------------------------------------------- ALTER TABLE "SYSTEM"."B" ADD ("EXTRA" VARCHAR2(50)) ALTER TABLE "SYSTEM"."B" MODIFY ("ID" NUMBER(10,0)) ALTER TABLE "SYSTEM"."B" RENAME TO "C" . |
DBMS_METADATA has also XML extensions like the following examples demonstrate.
SQL> SELECT dbms_metadata.compare_alter_xml('TABLE','A','B',USER,USER) FROM dual; DBMS_METADATA.COMPARE_ALTER_XML('TABLE','A','B',USER,USER) --------------------------------------------------------------------------------------------- <alter_xml xmlns="http://xmlns.oracle.com/ku" version="1.0"> <object_type>TABLE</object_type> <object1> <schema>SYSTEM</schema> <name>A</name> </object1> <object2> <schema>SYSTEM</schema> <name>B</name> </object2> <alter_list> <alter_list_item> <sql_list> <sql_list_item>ALTER TABLE "SYSTEM"."A" MODIFY ("ID" NUMBER(15,0))</sql_list_item> </sql_list> </alter_list_item> <alter_list_item> <sql_list> <sql_list_item>ALTER TABLE "SYSTEM"."A" RENAME TO "B"</sql_list_item> </sql_list> </alter_list_item> </alter_list> </alter_xml> SQL> SELECT dbms_metadata.compare_sxml('TABLE','A','B',USER,USER) FROM dual; DBMS_METADATA.COMPARE_SXML('TABLE','A','B',USER,USER) -------------------------------------------------------------------------------- <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0"> <schema>SYSTEM</schema> <name value1="A">B</name> <relational_table> <col_list> <col_list_item> <name>ID</name> <datatype>NUMBER</datatype> <PRECISION value1="10">15</precision> <scale>0</scale> </col_list_item> </col_list> <physical_properties> <heap_table> <segment_attributes> <pctfree>10</pctfree> <pctused>40</pctused> <initrans>1</initrans> <storage> <initial>65536</initial> <next>1048576</next> <minextents>1</minextents> <maxextents>2147483645</maxextents> <pctincrease>0</pctincrease> <freelists>1</freelists> <freelist_groups>1</freelist_groups> <buffer_pool>DEFAULT</buffer_pool> </storage> <tablespace>SYSTEM</tablespace> <logging>Y</logging> </segment_attributes> <compress>N</compress> </heap_table> </physical_properties> </relational_table> </table> SQL> SELECT dbms_metadata.get_sxml('TABLE', 'A', USER) FROM dual; DBMS_METADATA.GET_SXML('TABLE','A',USER) -------------------------------------------------------------------------------- <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0"> <schema>SYSTEM</schema> <name>A</name> <relational_table> <col_list> <col_list_item> <name>ID</name> <datatype>NUMBER</datatype> <precision>10</precision> <scale>0</scale> </col_list_item> </col_list> <physical_properties> <heap_table> <segment_attributes> <pctfree>10</pctfree> <pctused>40</pctused> <initrans>1</initrans> <storage> <initial>65536</initial> <next>1048576</next> <minextents>1</minextents> <maxextents>2147483645</maxextents> <pctincrease>0</pctincrease> <freelists>1</freelists> <freelist_groups>1</freelist_groups> <buffer_pool>DEFAULT</buffer_pool> </storage> <tablespace>SYSTEM</tablespace> <logging>Y</logging> </segment_attributes> <compress>N</compress> </heap_table> </physical_properties> </relational_table> </table> . |
DBMS_METADATA can also be used in different ways and or to alter its output. A small issue here is that this is only applicable to the DBMS_METADATA.GET_DDL functionality of DBMS_METADATA.
-- -- dbms_metadata.set_transform_param -- -- http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_metada.htm#i1000135 SQL> EXEC dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'STORAGE', FALSE); PL/SQL PROCEDURE successfully completed. SQL> SELECT dbms_metadata.get_ddl('TABLE','A') FROM dual; DBMS_METADATA.GET_DDL('TABLE','A') -------------------------------------------------------------------------------- CREATE TABLE "SYSTEM"."A" ( "ID" NUMBER(10,0) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "SYSTEM" SQL> EXEC dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'DEFAULT'); PL/SQL PROCEDURE successfully completed. SQL> SELECT dbms_metadata.get_ddl('TABLE','A') FROM dual; DBMS_METADATA.GET_DDL('TABLE','A') -------------------------------------------------------------------------------- CREATE TABLE "SYSTEM"."A" ( "ID" NUMBER(10,0) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" SQL> EXEC dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', FALSE); PL/SQL PROCEDURE successfully completed. SQL> SELECT dbms_metadata.get_ddl('TABLE','A') FROM dual; DBMS_METADATA.GET_DDL('TABLE','A') -------------------------------------------------------------------------------- CREATE TABLE "SYSTEM"."A" ( "ID" NUMBER(10,0) ) . |
Comparing XML instances
OK, that was a brief intro. Now lets pinpoint on comparing relational objects regarding the differences in DDL. The DBMS_METADATA.SET_TRANSFORM_PARAM doesn’t work for others then GET_DDL. The following statements were immediately executed while the “SEGMENT_ATTRIBUTES” option was still FALSE in my SQL*Plus session.
-- -- dbms_metadata.set_transform_param ONLY for DDL statements... -- -- http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_metada.htm#i1019414 -- SQL> SELECT dbms_metadata.get_xml('TABLE','A') FROM dual; DBMS_METADATA.GET_XML('TABLE','A') -------------------------------------------------------------------------------- < ?xml version="1.0"?><rowset><row> <table_t> <vers_major>1</vers_major> <vers_minor>2 </vers_minor> <obj_num>58932</obj_num> <schema_obj> <obj_num>58932</obj_num> <dataobj_num>58932</dataobj_num> <owner_num>5</owner_num> <owner_name>SYSTEM</owner_name> <name>A</name> <namespace>1</namespace> <type_num>2</type_num> <type_name>TABLE</type_name> <ctime>2008-03-19 11:16:45</ctime> <mtime>2008-03-19 11:16:45</mtime> <stime>2008-03-19 11:16:45</stime> <status>1</status> <flags>0</flags> <spare1>6</spare1> <spare2>1</spare2> <spare3>5</spare3> </schema_obj> <storage> <file_num>1</file_num> <block_num>65369</block_num> <type_num>5</type_num> <ts_num>0</ts_num> <blocks>8</blocks> <extents>1</extents> <iniexts>8</iniexts> <minexts>1</minexts> <maxexts>2147483645</maxexts> <extsize>128</extsize> <extpct>0</extpct> <user_num>5</user_num> <lists>1</lists> <groups>1</groups> <bitmapranges>2147483645</bitmapranges> <cachehint>0</cachehint> <scanhint>0</scanhint> <hwmincr>58932</hwmincr> <flags>4325377</flags> </storage> <ts_name>SYSTEM</ts_name> <blocksize>8192</blocksize> <dataobj_num>58932</dataobj_num> <cols>1</cols> <pct_free>10</pct_free> <pct_used>40</pct_used> <initrans>1</initrans> <maxtrans>255</maxtrans> <flags>1</flags> <audit_val>--------------------------------------</audit_val> <intcols>1</intcols> <kernelcols>1</kernelcols> <property>536870912</property> <property2>0</property2> <xmlschemacols>N</xmlschemacols> <trigflag>0</trigflag> <spare1>736</spare1> <spare6>19-MAR-08</spare6> <col_list> <col_list_item> <obj_num>58932</obj_num> <col_num>1</col_num> <intcol_num>1</intcol_num> <segcol_num>1</segcol_num> <property>0</property> <name>ID</name> <type_num>2</type_num> <length>22</length> <precision_num>10</precision_num> <scale>0</scale> <not_null>0</not_null> <charsetid>0</charsetid> <charsetform>0</charsetform> <base_intcol_num>1</base_intcol_num> <base_col_type>0</base_col_type> <spare1>0</spare1> <spare2>0</spare2> <spare3>0</spare3> </col_list_item> </col_list> <con0_list /> <con1_list /> <con2_list /> <refpar_level>0</refpar_level> </table_t> </row></rowset> SQL> SELECT dbms_metadata.get_xml('TABLE','B') FROM dual; DBMS_METADATA.GET_XML('TABLE','B') -------------------------------------------------------------------------------- < ?xml version="1.0"?><rowset><row> <table_t> <vers_major>1</vers_major> <vers_minor>2 </vers_minor> <obj_num>58933</obj_num> <schema_obj> <obj_num>58933</obj_num> <dataobj_num>58933</dataobj_num> <owner_num>5</owner_num> <owner_name>SYSTEM</owner_name> <name>B</name> <namespace>1</namespace> <type_num>2</type_num> <type_name>TABLE</type_name> <ctime>2008-03-19 11:17:05</ctime> <mtime>2008-03-19 11:17:05</mtime> <stime>2008-03-19 11:17:05</stime> <status>1</status> <flags>0</flags> <spare1>6</spare1> <spare2>1</spare2> <spare3>5</spare3> </schema_obj> <storage> <file_num>1</file_num> <block_num>65377</block_num> <type_num>5</type_num> <ts_num>0</ts_num> <blocks>8</blocks> <extents>1</extents> <iniexts>8</iniexts> <minexts>1</minexts> <maxexts>2147483645</maxexts> <extsize>128</extsize> <extpct>0</extpct> <user_num>5</user_num> <lists>1</lists> <groups>1</groups> <bitmapranges>2147483645</bitmapranges> <cachehint>0</cachehint> <scanhint>0</scanhint> <hwmincr>58933</hwmincr> <flags>4325377</flags> </storage> <ts_name>SYSTEM</ts_name> <blocksize>8192</blocksize> <dataobj_num>58933</dataobj_num> <cols>1</cols> <pct_free>10</pct_free> <pct_used>40</pct_used> <initrans>1</initrans> <maxtrans>255</maxtrans> <flags>1</flags> <audit_val>--------------------------------------</audit_val> <intcols>1</intcols> <kernelcols>1</kernelcols> <property>536870912</property> <property2>0</property2> <xmlschemacols>N</xmlschemacols> <trigflag>0</trigflag> <spare1>736</spare1> <spare6>19-MAR-08</spare6> <col_list> <col_list_item> <obj_num>58933</obj_num> <col_num>1</col_num> <intcol_num>1</intcol_num> <segcol_num>1</segcol_num> <property>0</property> <name>ID</name> <type_num>2</type_num> <length>22</length> <precision_num>15</precision_num> <scale>0</scale> <not_null>0</not_null> <charsetid>0</charsetid> <charsetform>0</charsetform> <base_intcol_num>1</base_intcol_num> <base_col_type>0</base_col_type> <spare1>0</spare1> <spare2>0</spare2> <spare3>0</spare3> </col_list_item> </col_list> <con0_list /> <con1_list /> <con2_list /> <refpar_level>0</refpar_level> </table_t> </row></rowset> . |
So how useful is a XMLDIFF and/or does the idea work? After fiddling a little bit with the syntax, I got the following output.
SQL> DESC a Name NULL? TYPE ----------------------------------------- -------- ---------------------------- ID NUMBER(10) SQL> DESC b Name NULL? TYPE ----------------------------------------- -------- ---------------------------- ID NUMBER(15) SQL> EXEC dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'DEFAULT'); PL/SQL PROCEDURE successfully completed. SQL> SELECT XMLDIFF( 2 xmltype(dbms_metadata.get_xml('TABLE','A')), 3 xmltype(dbms_metadata.get_xml('TABLE','B')) 4 ) AS "DIFFERENCES" 5 FROM dual; DIFFERENCES -------------------------------------------------------------------------------- <xd :xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> < ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?> </xd><xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58933</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58933</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/DATAOBJ_NUM[1]/text()[1]"> </xd><xd :content>58933</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"> </xd><xd :content>B</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/text()[1]"> </xd><xd :content>2008-03-19 11:17:05</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/text()[1]"> </xd><xd :content>2008-03-19 11:17:05</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/STIME[1]/text()[1]"> </xd><xd :content>2008-03-19 11:17:05</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/BLOCK_NUM[1]/text()[1]"> </xd><xd :content>65377</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/HWMINCR[1]/text()[1]"> </xd><xd :content>58933</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DATAOBJ_NUM[1]/text()[1]"> </xd><xd :content>58933</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_ITEM[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58933</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_ITEM[1]/PRECISION_NUM[1]/text()[1]"> </xd><xd :content>15</xd> SQL> SELECT XMLDIFF( 2 xmltype(dbms_metadata.get_xml('TABLE','B')), 3 xmltype(dbms_metadata.get_xml('TABLE','A')) 4 ) AS "DIFFERENCES" 5 FROM dual; DIFFERENCES -------------------------------------------------------------------------------- <xd :xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> < ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-al gorithm="global"?> </xd><xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/DATAOBJ_NUM[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"> </xd><xd :content>A</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/text()[1]"> </xd><xd :content>2008-03-19 11:16:45</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/text()[1]"> </xd><xd :content>2008-03-19 11:16:45</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/STIME[1]/text()[1]"> </xd><xd :content>2008-03-19 11:16:45</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/BLOCK_NUM[1]/text()[1]"> </xd><xd :content>65369</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/HWMINCR[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DATAOBJ_NUM[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_ITEM[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_ITEM[1]/PRECISION_NUM[1]/text()[1]"> </xd><xd :content>10</xd> . |
So in all that was easy.
But be aware (as stated here):
- XmlDiff ignores differences in the order of attributes while doing the comparison.
- XmlDiff ignores DocType declarations. Files are not validated against the DTD.
- XmlDiff ignores any differences in the namespace prefixes as long as the namespace prefixes refer to the same namespace URI. Otherwise, if two nodes have the same local name and content but differ in namespace URI, these differences are indicated.
Also be aware regarding the following examples, that XMLTABLE (and XQUERY) support XPath version 2 and the XML operators like EXTRACT, EXTRACTVALUE and EXISTSNODE, only support XPath version 1 (until now of course, it may change with new versions).
Making it more readable, I got the following output.
SQL> WITH tabxml AS 2 (SELECT XMLDIFF( 3 xmltype(dbms_metadata.get_xml('TABLE','A')), 4 xmltype(dbms_metadata.get_xml('TABLE','B')) 5 ) xmlset 6 FROM dual) 7 SELECT u.element_name 8 , u.element_value 9 FROM tabxml 10 , XMLTABLE 11 (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd") 12 ,'//xd:update-node' 13 PASSING xmlset 14 COLUMNS element_name xmltype PATH '//xd:update-node/@xd:xpath' 15 , element_value xmltype PATH '//xd:content/text()' 16 ) u 17 ; ELEMENT_NAME -------------------------------------------------------------------------------- ELEMENT_VALUE -------------------------------------------------------------------------------- /ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ_NUM[1]/text()[1] 58933 /ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/OBJ_NUM[1]/text()[1] 58933 /ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/DATAOBJ_NUM[1]/text()[1] 58933 /ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1] B /ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/text()[1] 2008-03-19 11:17:05 /ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/text()[1] 2008-03-19 11:17:05 /ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/STIME[1]/text()[1] 2008-03-19 11:17:05 /ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/BLOCK_NUM[1]/text()[1] 65377 /ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/HWMINCR[1]/text()[1] 58933 /ROWSET[1]/ROW[1]/TABLE_T[1]/DATAOBJ_NUM[1]/text()[1] 58933 /ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_ITEM[1]/OBJ_NUM[1]/text()[1] 58933 /ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_ITEM[1]/PRECISION_NUM[1]/text( )[1] 15 12 ROWS selected. SQL> WITH tabxml AS 2 (SELECT XMLDIFF( 3 xmltype(dbms_metadata.get_xml('TABLE','A')), 4 xmltype(dbms_metadata.get_xml('TABLE','B')) 5 ) xmlset 6 FROM dual) 7 SELECT u.element_name 8 , u.element_value 9 FROM tabxml 10 , XMLTABLE 11 (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd") 12 ,'//xd:update-node' 13 PASSING xmlset 14 COLUMNS element_name xmltype PATH '//xd:update-node[@xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"]' 15 , element_value xmltype PATH '//xd:content/text()' 16 ) u 17 WHERE u.element_name IS NOT NULL 18 ; ELEMENT_NAME -------------------------------------------------------------------------------- ELEMENT_VALUE -------------------------------------------------------------------------------- <xd :update-node xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xd:node-TYPE="t ext" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"></xd><xd :content>B</xd> B SQL> col NAME FOR a60 SQL> col VALUE FOR a20 SQL> WITH tabxml AS 2 (SELECT XMLDIFF( 3 xmltype(dbms_metadata.get_xml('TABLE','A')), 4 xmltype(dbms_metadata.get_xml('TABLE','B')) 5 ) xmlset 6 FROM dual) 7 SELECT EXTRACT(u.element_name,'//@xd:xpath','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"') AS "NAME" 8 , u.element_value AS "VALUE" 9 FROM tabxml 10 , XMLTABLE 11 (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd") 12 ,'//xd:update-node' 13 PASSING xmlset 14 COLUMNS element_name xmltype PATH '//xd:update-node[@xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"]' 15 , element_value xmltype PATH '//xd:content/text()' 16 ) u 17 WHERE u.element_name IS NOT NULL 18 ; NAME VALUE ------------------------------------------------------------ -------------------- /ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1] B . |
Searching for Differences
Using this method, I tried to see if I could create a diff between both tables, in a more readable form.
SQL> SELECT XMLCONCAT(XMLDIFF( 2 xmltype(dbms_metadata.get_xml('TABLE','A')), 3 xmltype(dbms_metadata.get_xml('TABLE','B')) 4 ), 5 XMLDIFF( 6 xmltype(dbms_metadata.get_xml('TABLE','B')), 7 xmltype(dbms_metadata.get_xml('TABLE','A')) 8 ) ) 9 FROM dual; XMLCONCAT(XMLDIFF(XMLTYPE(DBMS_METADATA.GET_XML('TABLE','A')),XMLTYPE(DBMS_METAD -------------------------------------------------------------------------------- <xd :xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns .oracle.com/xdb/xdiff.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmln s:xsi="http://www.w3.org/2001/XMLSchema-instance"> < ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-al gorithm="global"?> </xd><xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ _NUM[1]/text()[1]"> </xd><xd :content>58933</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCH EMA_OBJ[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58933</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCH EMA_OBJ[1]/DATAOBJ_NUM[1]/text()[1]"> </xd><xd :content>58933</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCH EMA_OBJ[1]/NAME[1]/text()[1]"> </xd><xd :content>B</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCH EMA_OBJ[1]/CTIME[1]/text()[1]"> </xd><xd :content>2008-03-19 11:17:05</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCH EMA_OBJ[1]/MTIME[1]/text()[1]"> </xd><xd :content>2008-03-19 11:17:05</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCH EMA_OBJ[1]/STIME[1]/text()[1]"> </xd><xd :content>2008-03-19 11:17:05</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STO RAGE[1]/BLOCK_NUM[1]/text()[1]"> </xd><xd :content>65377</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STO RAGE[1]/HWMINCR[1]/text()[1]"> </xd><xd :content>58933</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DAT AOBJ_NUM[1]/text()[1]"> </xd><xd :content>58933</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL _LIST[1]/COL_LIST_ITEM[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58933</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL _LIST[1]/COL_LIST_ITEM[1]/PRECISION_NUM[1]/text()[1]"> </xd><xd :content>15</xd> <xd :xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns .oracle.com/xdb/xdiff.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmln s:xsi="http://www.w3.org/2001/XMLSchema-instance"> < ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-al gorithm="global"?> </xd><xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ _NUM[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCH EMA_OBJ[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCH EMA_OBJ[1]/DATAOBJ_NUM[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCH EMA_OBJ[1]/NAME[1]/text()[1]"> </xd><xd :content>A</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCH EMA_OBJ[1]/CTIME[1]/text()[1]"> </xd><xd :content>2008-03-19 11:16:45</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCH EMA_OBJ[1]/MTIME[1]/text()[1]"> </xd><xd :content>2008-03-19 11:16:45</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCH EMA_OBJ[1]/STIME[1]/text()[1]"> </xd><xd :content>2008-03-19 11:16:45</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STO RAGE[1]/BLOCK_NUM[1]/text()[1]"> </xd><xd :content>65369</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STO RAGE[1]/HWMINCR[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DAT AOBJ_NUM[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL _LIST[1]/COL_LIST_ITEM[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL _LIST[1]/COL_LIST_ITEM[1]/PRECISION_NUM[1]/text()[1]"> </xd><xd :content>10</xd> SQL> WITH tabxml AS 2 ( SELECT XMLCONCAT(XMLDIFF( 3 xmltype(dbms_metadata.get_xml('TABLE','A')), 4 xmltype(dbms_metadata.get_xml('TABLE','B')) 5 ), 6 XMLDIFF( 7 xmltype(dbms_metadata.get_xml('TABLE','B')), 8 xmltype(dbms_metadata.get_xml('TABLE','A')) 9 ) ) xmlset 10 FROM DUAL) 11 SELECT EXTRACT(u.element_name,'//@xd:xpath','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"') AS "NAME" 12 , u.element_value AS "VALUE" 13 FROM tabxml 14 , XMLTABLE 15 (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd") 16 ,'//xd:update-node' 17 PASSING xmlset 18 COLUMNS element_name xmltype PATH '//xd:update-node[@xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"]' 19 , element_value xmltype PATH '//xd:content/text()' 20 ) u 21 WHERE u.element_name IS NOT NULL 22 ; NAME VALUE ------------------------------------------------------------ -------------------- /ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1] B /ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1] A SQL> -- Being a little bit more preciese (always good for your performance) SQL> WITH tabxml AS 2 ( SELECT XMLCONCAT(XMLDIFF( 3 xmltype(dbms_metadata.get_xml('TABLE','A')), 4 xmltype(dbms_metadata.get_xml('TABLE','B')) 5 ), 6 XMLDIFF( 7 xmltype(dbms_metadata.get_xml('TABLE','B')), 8 xmltype(dbms_metadata.get_xml('TABLE','A')) 9 ) ) xmlset 10 FROM DUAL) 11 SELECT EXTRACT(u.element_name,'//@xd:xpath','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"') AS "NAME" 12 , u.element_value AS "VALUE" 13 FROM tabxml 14 , XMLTABLE 15 (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd") 16 ,'/xd:xdiff/xd:update-node' 17 PASSING xmlset 18 COLUMNS element_name xmltype PATH '/xd:update-node[@xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"]' 19 , element_value xmltype PATH '/xd:update-node/xd:content/text()' 20 ) u 21 WHERE u.element_name IS NOT NULL 22 ; NAME VALUE ------------------------------------------------------------ -------------------- /ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1] B /ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1] A . |
The next step now was: “what will happen if I do a diff between table A and C” (table C had an extra column, remember)?
SQL> SELECT XMLCONCAT(XMLDIFF( 2 xmltype(dbms_metadata.get_xml('TABLE','A')), 3 xmltype(dbms_metadata.get_xml('TABLE','C')) 4 ), 5 XMLDIFF( 6 xmltype(dbms_metadata.get_xml('TABLE','C')), 7 xmltype(dbms_metadata.get_xml('TABLE','A')) 8 ) ) 9 FROM DUAL 10 ; XMLCONCAT(XMLDIFF(XMLTYPE(DBMS_METADATA.GET_XML('TABLE','A')),XMLTYPE(DBMS_METADATA.GET_XML('TABLE', ---------------------------------------------------------------------------------------------------- <xd :xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdif f.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema- instance"> < ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?> </xd><xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58935</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/OBJ_NUM[1 ]/text()[1]"> </xd><xd :content>58935</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/DATAOBJ_N UM[1]/text()[1]"> </xd><xd :content>58935</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/t ext()[1]"> </xd><xd :content>C</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/ text()[1]"> </xd><xd :content>2008-03-19 15:08:47</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/ text()[1]"> </xd><xd :content>2008-03-19 15:08:47</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/STIME[1]/ text()[1]"> </xd><xd :content>2008-03-19 15:08:47</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/BLOCK_NUM[1] /text()[1]"> </xd><xd :content>65385</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/HWMINCR[1]/t ext()[1]"> </xd><xd :content>58935</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DATAOBJ_NUM[1]/text()[1 ]"> </xd><xd :content>58935</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COLS[1]/text()[1]"> </xd><xd :content>2</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/INTCOLS[1]/text()[1]"> </xd><xd :content>2</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/KERNELCOLS[1]/text()[1] "> </xd><xd :content>2</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_IT EM[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58935</xd> <xd :append-node xd:node-TYPE="element" xd:parent-xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]"> </xd><xd :content> <col_list_item> <obj_num>58935</obj_num> <col_num>2</col_num> <intcol_num>2</intcol_num> <segcol_num>2</segcol_num> <property>0</property> <name>EXTRA</name> <type_num>1</type_num> <length>50</length> <not_null>0</not_null> <charsetid>873</charsetid> <charsetform>1</charsetform> <base_intcol_num>2</base_intcol_num> <base_col_type>0</base_col_type> <spare1>0</spare1> <spare2>0</spare2> <spare3>50</spare3> </col_list_item> </xd> <xd :xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdif f.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema- instance"> < ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?> </xd><xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/OBJ_NUM[1 ]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/DATAOBJ_N UM[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/t ext()[1]"> </xd><xd :content>A</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/ text()[1]"> </xd><xd :content>2008-03-19 11:16:45</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/ text()[1]"> </xd><xd :content>2008-03-19 11:16:45</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/STIME[1]/ text()[1]"> </xd><xd :content>2008-03-19 11:16:45</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/BLOCK_NUM[1] /text()[1]"> </xd><xd :content>65369</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/HWMINCR[1]/t ext()[1]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DATAOBJ_NUM[1]/text()[1 ]"> </xd><xd :content>58932</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COLS[1]/text()[1]"> </xd><xd :content>1</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/INTCOLS[1]/text()[1]"> </xd><xd :content>1</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/KERNELCOLS[1]/text()[1] "> </xd><xd :content>1</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_IT EM[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58932</xd> <xd :delete-node xd:node-TYPE="element" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST _ITEM[2]"/> SQL> col LENGTH FOR a10 SQL> WITH tabxml AS 2 ( SELECT XMLCONCAT(XMLDIFF( 3 xmltype(dbms_metadata.get_xml('TABLE','A')), 4 xmltype(dbms_metadata.get_xml('TABLE','C')) 5 ), 6 XMLDIFF( 7 xmltype(dbms_metadata.get_xml('TABLE','C')), 8 xmltype(dbms_metadata.get_xml('TABLE','A')) 9 ) ) xmlset 10 FROM DUAL) 11 SELECT EXTRACT(v.append_name,'//@xd:parent-xpath','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"') AS "NAME" 12 , EXTRACT(v.append_value,'//COL_LIST_ITEM/NAME/text()','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"') AS "VALUE" 13 , EXTRACT(v.append_value,'//COL_LIST_ITEM/LENGTH/text()','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"') AS "LENGTH" 14 FROM tabxml 15 , XMLTABLE 16 (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd") 17 ,'/xd:xdiff/xd:append-node' 18 PASSING xmlset 19 COLUMNS append_name xmltype PATH '/xd:append-node[@xd:parent-xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]"]' 20 , append_value xmltype PATH '/xd:append-node/xd:content' 21 ) v 22 WHERE v.append_name IS NOT NULL 23 ; NAME VALUE LENGTH ------------------------------------------------------------ -------------------- ---------- /ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1] EXTRA 50 . |
XMLTABLE and XQuery wizards will probably do a better job and / or people will use XMLTRANSFORM or XSLT (an XML Stylesheet) to create a better report. But it works, at least for tables, but does it also work, for instance, for procedures. So lets give it a try…
SQL> DROP PROCEDURE proc_01; PROCEDURE dropped. SQL> SQL> CREATE OR REPLACE PROCEDURE proc_01 2 AS 3 BEGIN 4 NULL; 5 END; 6 / PROCEDURE created. SQL> SQL> DROP PROCEDURE proc_02; PROCEDURE dropped. SQL> SQL> CREATE OR REPLACE PROCEDURE proc_02 2 AS 3 BEGIN 4 NULL; 5 -- this is extra 6 END; 7 / PROCEDURE created. SQL> SELECT dbms_metadata.get_ddl('PROCEDURE','PROC_01') FROM dual; DBMS_METADATA.GET_DDL('PROCEDURE','PROC_01') ---------------------------------------------------------------------------- CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_01" AS BEGIN NULL; END; SQL> SELECT dbms_metadata.get_ddl('PROCEDURE','PROC_02') FROM dual; DBMS_METADATA.GET_DDL('PROCEDURE','PROC_02') ------------------------------------------------------------------------ CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_02" AS BEGIN NULL; -- this is extra END; . |
Using DBMS_METADATA.GET_XML will give the following output.
SQL> SELECT dbms_metadata.get_xml('PROCEDURE','PROC_01') FROM dual; DBMS_METADATA.GET_XML('PROCEDURE','PROC_01') -------------------------------------------------------------------------------- < ?xml version="1.0"?><rowset><row> <procedure_t> <vers_major>1</vers_major> <vers_minor>1 </vers_minor> <obj_num>58937</obj_num> <type_num>7</type_num> <schema_obj> <obj_num>58937</obj_num> <owner_num>5</owner_num> <owner_name>SYSTEM</owner_name> <name>PROC_01</name> <namespace>1</namespace> <type_num>7</type_num> <type_name>PROCEDURE</type_name> <ctime>2008-03-19 16:39:34</ctime> <mtime>2008-03-19 16:39:34</mtime> <stime>2008-03-19 16:39:34</stime> <status>1</status> <flags>0</flags> <spare1>6</spare1> <spare2>65535</spare2> <spare3>5</spare3> </schema_obj> <source_lines> <source_lines_item> <obj_num>58937</obj_num> <line>1</line> <pre_name>0</pre_name> <post_name_off>18</post_name_off> <post_keyw>11</post_keyw> <pre_name_len>0</pre_name_len> <source>PROCEDURE proc_01 </source> </source_lines_item> <source_lines_item> <obj_num>58937</obj_num> <line>2</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <source>AS </source> </source_lines_item> <source_lines_item> <obj_num>58937</obj_num> <line>3</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <source>BEGIN </source> </source_lines_item> <source_lines_item> <obj_num>58937</obj_num> <line>4</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <source> NULL; </source> </source_lines_item> <source_lines_item> <obj_num>58937</obj_num> <line>5</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <source>END;</source> </source_lines_item> </source_lines> </procedure_t> </row></rowset> SQL> SELECT dbms_metadata.get_xml('PROCEDURE','PROC_02') FROM dual; DBMS_METADATA.GET_XML('PROCEDURE','PROC_02') -------------------------------------------------------------------------------- < ?xml version="1.0"?><rowset><row> <procedure_t> <vers_major>1</vers_major> <vers_minor>1 </vers_minor> <obj_num>58938</obj_num> <type_num>7</type_num> <schema_obj> <obj_num>58938</obj_num> <owner_num>5</owner_num> <owner_name>SYSTEM</owner_name> <name>PROC_02</name> <namespace>1</namespace> <type_num>7</type_num> <type_name>PROCEDURE</type_name> <ctime>2008-03-19 16:39:34</ctime> <mtime>2008-03-19 16:39:34</mtime> <stime>2008-03-19 16:39:34</stime> <status>1</status> <flags>0</flags> <spare1>6</spare1> <spare2>65535</spare2> <spare3>5</spare3> </schema_obj> <source_lines> <source_lines_item> <obj_num>58938</obj_num> <line>1</line> <pre_name>0</pre_name> <post_name_off>18</post_name_off> <post_keyw>11</post_keyw> <pre_name_len>0</pre_name_len> <source>PROCEDURE proc_02 </source> </source_lines_item> <source_lines_item> <obj_num>58938</obj_num> <line>2</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <source>AS </source> </source_lines_item> <source_lines_item> <obj_num>58938</obj_num> <line>3</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <source>BEGIN </source> </source_lines_item> <source_lines_item> <obj_num>58938</obj_num> <line>4</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <source> NULL; </source> </source_lines_item> <source_lines_item> <obj_num>58938</obj_num> <line>5</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <source>-- this is extra </source> </source_lines_item> <source_lines_item> <obj_num>58938</obj_num> <line>6</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <source>END;</source> </source_lines_item> </source_lines> </procedure_t> </row></rowset> . |
Appling the same XMLDIFF method as demonstrated before will give the following.
SQL> SELECT XMLDIFF( 2 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')), 3 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_02')) 4 ) AS "DIFFERENCES" 5 FROM dual; DIFFERENCES ---------------------------------------------------------------------------------------------------- <xd :xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdif f.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema- instance"> < ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?> </xd><xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58938</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58938</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"> </xd><xd :content>PROC_02</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58938</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[1]/SOURCE[1]/text()[1]"> </xd><xd :content>PROCEDURE proc_02 </xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[2]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58938</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[3]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58938</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[4]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58938</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[4]/SOURCE[1]/text()[1]"> </xd><xd :content> NULL; </xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58938</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]/SOURCE[1]/text()[1]"> </xd><xd :content>-- this is extra </xd> <xd :append-node xd:node-TYPE="element" xd:parent-xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]"> </xd><xd :content> <source_lines_item> <obj_num>58938</obj_num> <line>6</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <source>END;</source> </source_lines_item> </xd> SQL> SET LINES 200 SQL> col name FOR a100 SQL> col VALUE FOR a50 SQL> WITH tabxml AS 2 ( SELECT XMLDIFF( 3 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')), 4 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_02')) 5 ) xmlset 6 FROM dual) 7 SELECT EXTRACT(u.element_name,'//@xd:xpath','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"') AS "NAME" 8 , u.element_value AS "VALUE" 9 FROM tabxml 10 , XMLTABLE 11 (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd") 12 ,'//xd:update-node' 13 PASSING xmlset 14 COLUMNS element_name xmltype PATH '//xd:update-node[@xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]/SOURCE[1]/text()[1]"]' 15 , element_value xmltype PATH '//xd:content/text()' 16 ) u 17 WHERE u.element_name IS NOT NULL 18 ; NAME VALUE ---------------------------------------------------------------------------------------------------- -------------------------------------------------- /ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]/SOURCE[1]/text()[1] -- this is extra . |
What will happen if we make it a little bit more interesting…?
SQL> DROP PROCEDURE proc_03; PROCEDURE dropped. SQL> CREATE OR REPLACE PROCEDURE proc_03 2 AS 3 BEGIN 4 5 NULL; 6 7 dbms_output.put_line('Hello World'); 8 9 END; 10 / PROCEDURE created. SQL> SELECT XMLDIFF( 2 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')), 3 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_03')) 4 ) AS "DIFFERENCES" 5 FROM dual; DIFFERENCES -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- <xd :xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema- instance"> < ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?> </xd><xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58939</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58939</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"> </xd><xd :content>PROC_03</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/text()[1]"> </xd><xd :content>2008-03-19 17:08:26</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/text()[1]"> </xd><xd :content>2008-03-19 17:08:26</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/STIME[1]/text()[1]"> </xd><xd :content>2008-03-19 17:08:26</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[1]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58939</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[1]/SOURCE[1]/text()[1]"> </xd><xd :content>PROCEDURE proc_03 </xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[2]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58939</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[3]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58939</xd> <xd :insert-node-BEFORE xd:node-TYPE="element" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[4]"> </xd><xd :content> <source_lines_item> <obj_num>58939</obj_num> <line>4</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <SOURCE /> </source_lines_item> </xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[4]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58939</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[4]/LINE[1]/text()[1]"> </xd><xd :content>5</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[4]/SOURCE[1]/text()[1]"> </xd><xd :content> NULL; </xd> <xd :insert-node-BEFORE xd:node-TYPE="element" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]"> </xd><xd :content> <source_lines_item> <obj_num>58939</obj_num> <line>6</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <SOURCE /> </source_lines_item> </xd> <xd :insert-node-BEFORE xd:node-TYPE="element" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]"> </xd><xd :content> <source_lines_item> <obj_num>58939</obj_num> <line>7</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <source> dbms_output.put_line('Hello World'); </source> </source_lines_item> </xd> <xd :insert-node-BEFORE xd:node-TYPE="element" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]"> </xd><xd :content> <source_lines_item> <obj_num>58939</obj_num> <line>8</line> <pre_name>0</pre_name> <post_name_off>0</post_name_off> <post_keyw>0</post_keyw> <pre_name_len>0</pre_name_len> <SOURCE /> </source_lines_item> </xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]/OBJ_NUM[1]/text()[1]"> </xd><xd :content>58939</xd> <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]/LINE[1]/text()[1]"> </xd><xd :content>9</xd> . |
Be aware that I also put “empty” lines in the procedure. “Whitespace” is always tricky, so lets check.
The problem here is that lines 4,6 and 8 are described by a empty element [SOURCE/] (sorry for the [] brackets, my code plugin still has problems with XML tags). Whatever I tried, I didn’t get the wanted end result, as show below (but hopefully someone has an answer to this).
LINE TEXT
---- --------------------------------------------------
4
6
7 dbms_output.put_line('Hello World');
8
.
The following attempts were made by me, but didn’t gave me the wanted result.
SQL> WITH tabxml AS
2 ( SELECT XMLDIFF(
3 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')),
4 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_03'))
5 ) xmlset
6 FROM dual)
7 SELECT u.element_line AS "LINE"
8 , u.element_value AS "SOURCE"
9 FROM tabxml
10 , XMLTABLE
11 (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd")
12 ,'//xd:xdiff'
13 PASSING xmlset
14 COLUMNS element_name xmltype PATH '//xd:insert-node-before[@xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]"]'
15 , element_line xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/LINE/text()'
16 , element_value xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/SOURCE/text()'
17 ) u
18 WHERE u.element_name IS NOT NULL
19 ORDER BY 1
20 ;
SELECT u.element_line AS "LINE"
*
ERROR at line 7:
ORA-22950: cannot ORDER objects WITHOUT MAP OR ORDER method
SQL> WITH tabxml AS
2 ( SELECT XMLDIFF(
3 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')),
4 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_03'))
5 ) xmlset
6 FROM dual)
7 SELECT POSITION
8 , u.element_line AS "LINE"
9 , u.element_value AS "SOURCE"
10 FROM tabxml
11 , XMLTABLE
12 (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd")
13 ,'//xd:xdiff'
14 PASSING xmlset
15 COLUMNS POSITION FOR ORDINALITY
16 , element_name xmltype PATH '//xd:insert-node-before[@xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]"]'
17 , element_line xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/LINE/text()'
18 , element_value xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/SOURCE/text()'
19 ) u
20 WHERE u.element_name IS NOT NULL
21 ORDER BY 1
22 ;
POSITION LINE SOURCE
---------- ---- --------------------------------------------------
1 4678 dbms_output.put_line('Hello World');
SQL> WITH tabxml AS
2 ( SELECT XMLDIFF(
3 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')),
4 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_03'))
5 ) xmlset
6 FROM dual)
7 SELECT POSITION
8 , u.element_line AS "LINE"
9 , u.element_value AS "SOURCE"
10 FROM tabxml
11 , XMLTABLE
12 (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd")
13 ,'//xd:xdiff'
14 PASSING xmlset
15 COLUMNS POSITION FOR ORDINALITY
16 , element_line xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/LINE/text()'
17 , element_value xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/SOURCE/text()'
18 ) u
19 ORDER BY 1
20 ;
POSITION LINE SOURCE
---------- ---- --------------------------------------------------
1 4678 dbms_output.put_line('Hello World');
SQL> WITH tabxml AS
2 ( SELECT XMLDIFF(
3 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')),
4 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_03'))
5 ) xmlset
6 FROM dual)
7 SELECT POSITION
8 , u.element_line AS "LINE"
9 , u.element_value AS "SOURCE"
10 FROM tabxml
11 , XMLTABLE
12 (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd")
13 ,'//xd:xdiff'
14 PASSING xmlset
15 COLUMNS POSITION FOR ORDINALITY
16 , element_line xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/LINE/text()'
17 , element_value xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/SOURCE/text()'
18 ) u
19 WHERE existsnode(tabxml.xmlset,'//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/SOURCE'
20 ,'xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"'
21 ) = 1
22 ORDER BY 1
23 ;
POSITION LINE SOURCE
---------- ---- --------------------------------------------------
1 4678 dbms_output.put_line('Hello World');
.
Conclusion
Hmmm, mixed feelings. The principle works, but I am not yet happy enough with the end result. I guess that people (aka programmers) that are more experienced in the use with XSQL, XQuery etc can make this work. In the meantime I will try to achieve better results. If you managed and created a blog post about it. Drop your URL here via the comment facility.
I hope I have been of help (or inspired someone else – passed on the inspiration from Dominic / Laurent so to say…).
Grz
Marco
Further Reading
- XDiff XML Schema
- XMLDIFF Reference
- DBMS_METADATA.GET_XXX functions Reference
- OTN XMLDB Forum: The usefulness (?) of XMLDIFF in database change management
- Laurent Schneider: Pivot Table – Part 2
- Dominic Delmolino: 2008 Hotsos Conference Material
Download the used script here:
1 comment
Marco Gralike
4 February, 2012 at 16:39 (UTC 1) Link to this comment
Some useful extra info regarding XMLDIFF see OTN Forum thread discussion here:
https://forums.oracle.com/forums/thread.jspa?forumID=34&threadID=2339668
M.