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.


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"


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.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)
   )

-- 
-- 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>



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:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ_NUM[1]/text()[1]">
    <xd:content>58933</xd:content>
  </xd:update-node>
  <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:content>58933</xd:content>
  </xd:update-node>
  <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:content>58933</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]">
    <xd:content>B</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/text()[1]">
    <xd:content>2008-03-19 11:17:05</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/text()[1]">
    <xd:content>2008-03-19 11:17:05</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/STIME[1]/text()[1]">
    <xd:content>2008-03-19 11:17:05</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/BLOCK_NUM[1]/text()[1]">
    <xd:content>65377</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/HWMINCR[1]/text()[1]">
    <xd:content>58933</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DATAOBJ_NUM[1]/text()[1]">
    <xd:content>58933</xd:content>
  </xd:update-node>
  <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:content>58933</xd:content>
  </xd:update-node>
  <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:content>15</xd:content>
  </xd:update-node>
</xd:xdiff>

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:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ_NUM[1]/text()[1]">
    <xd:content>58932</xd:content>
  </xd:update-node>
  <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:content>58932</xd:content>
  </xd:update-node>
  <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:content>58932</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]">
    <xd:content>A</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/text()[1]">
    <xd:content>2008-03-19 11:16:45</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/text()[1]">
    <xd:content>2008-03-19 11:16:45</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/STIME[1]/text()[1]">
    <xd:content>2008-03-19 11:16:45</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/BLOCK_NUM[1]/text()[1]">
    <xd:content>65369</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/HWMINCR[1]/text()[1]">
    <xd:content>58932</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DATAOBJ_NUM[1]/text()[1]">
    <xd:content>58932</xd:content>
  </xd:update-node>
  <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:content>58932</xd:content>
  </xd:update-node>
  <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:content>10</xd:content>
  </xd:update-node>
</xd:xdiff>

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
:content>B</xd:content></xd:update-node>

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



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:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ
_NUM[1]/text()[1]">
    <xd:content>58933</xd:content>
  </xd:update-node>
  <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:content>58933</xd:content>
  </xd:update-node>
  <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:content>58933</xd:content>
  </xd:update-node>
  <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:content>B</xd:content>
  </xd:update-node>
  <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:content>2008-03-19 11:17:05</xd:content>
  </xd:update-node>
  <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:content>2008-03-19 11:17:05</xd:content>
  </xd:update-node>
  <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:content>2008-03-19 11:17:05</xd:content>
  </xd:update-node>
  <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:content>65377</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STO
RAGE[1]/HWMINCR[1]/text()[1]">
    <xd:content>58933</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DAT
AOBJ_NUM[1]/text()[1]">
    <xd:content>58933</xd:content>
  </xd:update-node>
  <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:content>58933</xd:content>
  </xd:update-node>
  <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:content>15</xd:content>
  </xd:update-node>
</xd:xdiff>
<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:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ
_NUM[1]/text()[1]">
    <xd:content>58932</xd:content>
  </xd:update-node>
  <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:content>58932</xd:content>
  </xd:update-node>
  <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:content>58932</xd:content>
  </xd:update-node>
  <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:content>A</xd:content>
  </xd:update-node>
  <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:content>2008-03-19 11:16:45</xd:content>
  </xd:update-node>
  <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:content>2008-03-19 11:16:45</xd:content>
  </xd:update-node>
  <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:content>2008-03-19 11:16:45</xd:content>
  </xd:update-node>
  <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:content>65369</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STO
RAGE[1]/HWMINCR[1]/text()[1]">
    <xd:content>58932</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DAT
AOBJ_NUM[1]/text()[1]">
    <xd:content>58932</xd:content>
  </xd:update-node>
  <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:content>58932</xd:content>
  </xd:update-node>
  <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:content>10</xd:content>
  </xd:update-node>
</xd:xdiff>



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> 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


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:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ_NUM[1]/text()[1]">
    <xd:content>58935</xd:content>
  </xd:update-node>
  <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:content>58935</xd:content>
  </xd:update-node>
  <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:content>58935</xd:content>
  </xd:update-node>
  <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:content>C</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/
text()[1]">
    <xd:content>2008-03-19 15:08:47</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/
text()[1]">
    <xd:content>2008-03-19 15:08:47</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/STIME[1]/
text()[1]">
    <xd:content>2008-03-19 15:08:47</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/BLOCK_NUM[1]
/text()[1]">
    <xd:content>65385</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/HWMINCR[1]/t
ext()[1]">
    <xd:content>58935</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DATAOBJ_NUM[1]/text()[1
]">
    <xd:content>58935</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COLS[1]/text()[1]">
    <xd:content>2</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/INTCOLS[1]/text()[1]">
    <xd:content>2</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/KERNELCOLS[1]/text()[1]
">
    <xd:content>2</xd:content>
  </xd:update-node>
  <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:content>58935</xd:content>
  </xd:update-node>
  <xd:append-node xd:node-type="element" xd:parent-xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]">

    <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:content>
  </xd:append-node>
</xd:xdiff>
<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:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ_NUM[1]/text()[1]">
    <xd:content>58932</xd:content>
  </xd:update-node>
  <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:content>58932</xd:content>
  </xd:update-node>
  <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:content>58932</xd:content>
  </xd:update-node>
  <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:content>A</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/
text()[1]">
    <xd:content>2008-03-19 11:16:45</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/
text()[1]">
    <xd:content>2008-03-19 11:16:45</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/STIME[1]/
text()[1]">
    <xd:content>2008-03-19 11:16:45</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/BLOCK_NUM[1]
/text()[1]">
    <xd:content>65369</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/HWMINCR[1]/t
ext()[1]">
    <xd:content>58932</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DATAOBJ_NUM[1]/text()[1
]">
    <xd:content>58932</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COLS[1]/text()[1]">
    <xd:content>1</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/INTCOLS[1]/text()[1]">
    <xd:content>1</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/KERNELCOLS[1]/text()[1]
">
    <xd:content>1</xd:content>
  </xd:update-node>
  <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:content>58932</xd:content>
  </xd:update-node>
  <xd:delete-node xd:node-type="element" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST
_ITEM[2]"/>
</xd:xdiff>


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



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;


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>


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:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/OBJ_NUM[1]/text()[1]">
    <xd:content>58938</xd:content>
  </xd:update-node>
  <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:content>58938</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]">
    <xd:content>PROC_02</xd:content>
  </xd:update-node>
  <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:content>58938</xd:content>
  </xd:update-node>
  <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:content>procedure proc_02
</xd:content>
  </xd:update-node>
  <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:content>58938</xd:content>
  </xd:update-node>
  <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:content>58938</xd:content>
  </xd:update-node>
  <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:content>58938</xd:content>
  </xd:update-node>
  <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:content>  NULL;
</xd:content>
  </xd:update-node>
  <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:content>58938</xd:content>
  </xd:update-node>
  <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:content>-- this is extra
</xd:content>
  </xd:update-node>
  <xd:append-node xd:node-type="element" xd:parent-xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]">
    <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:content>
  </xd:append-node>
</xd:xdiff>



SQL> set lines 200
SQL> col name for a100
SQL> col value for a50
SQL> 
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


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:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/OBJ_NUM[1]/text()[1]">
    <xd:content>58939</xd:content>
  </xd:update-node>
  <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:content>58939</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]">
    <xd:content>PROC_03</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/text()[1]">
    <xd:content>2008-03-19 17:08:26</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/text()[1]">
    <xd:content>2008-03-19 17:08:26</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/STIME[1]/text()[1]">
    <xd:content>2008-03-19 17:08:26</xd:content>
  </xd:update-node>
  <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:content>58939</xd:content>
  </xd:update-node>
  <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:content>procedure proc_03
</xd:content>
  </xd:update-node>
  <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:content>58939</xd:content>
  </xd:update-node>
  <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:content>58939</xd:content>
  </xd:update-node>
  <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: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:content>
  </xd:insert-node-before>
  <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:content>58939</xd:content>
  </xd:update-node>
  <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:content>5</xd:content>
  </xd:update-node>
  <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:content> NULL;
</xd:content>
  </xd:update-node>
  <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: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:content>
  </xd:insert-node-before>
  <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: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(&apos;Hello World&apos;);
</SOURCE>
      </SOURCE_LINES_ITEM>
    </xd:content>
  </xd:insert-node-before>
  <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: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:content>
  </xd:insert-node-before>
  <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:content>58939</xd:content>
  </xd:update-node>
  <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:content>9</xd:content>
  </xd:update-node>
</xd:xdiff>


SQL> col text for a50
SQL> col line for 999

SQL> select line, text
  2  from user_source 
  3  where name='PROC_03';

LINE TEXT
---- --------------------------------------------------
   1 procedure proc_03
   2 as
   3 begin
   4
   5  NULL;
   6
   7  dbms_output.put_line('Hello World');
   8
   9 end;

9 rows selected.

SQL> col LINE for a4
SQL> col source 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_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 u.element_line                                                                        
 20  ;           

LINE SOURCE
---- --------------------------------------------------
4678  dbms_output.put_line(&apos;Hello World&apos;);


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(&apos;Hello World&apos;);


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(&apos;Hello World&apos;);


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(&apos;Hello World&apos;);