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.
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)
---------------------------------------------------------------------------------------------
TABLESYSTEMASYSTEMBALTER TABLE "SYSTEM"."A" MODIFY ("ID" NUMBER(15,0))ALTER TABLE "SYSTEM"."A" RENAME TO "B"
SQL> SELECT dbms_metadata.compare_sxml('TABLE','A','B',USER,USER) from dual;
DBMS_METADATA.COMPARE_SXML('TABLE','A','B',USER,USER)
--------------------------------------------------------------------------------
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.
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"?>12 5893258932589325SYSTEMA12TABLE2008-03-19 11:16:452008-03-19 11:16:452008-03-19 11:16:451061516536950818121474836451280511214748364500589324325377SYSTEM8192589321104012551--------------------------------------115368709120N073619-MAR-08589321110ID222100000100000
SQL> select dbms_metadata.get_xml('TABLE','B') from dual;
DBMS_METADATA.GET_XML('TABLE','B')
--------------------------------------------------------------------------------
< ?xml version="1.0"?>12 5893358933589335SYSTEMB12TABLE2008-03-19 11:17:052008-03-19 11:17:052008-03-19 11:17:051061516537750818121474836451280511214748364500589334325377SYSTEM8192589331104012551--------------------------------------115368709120N073619-MAR-08589331110ID222150000100000
.
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
--------------------------------------------------------------------------------
< ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?>
589335893358933B2008-03-19 11:17:052008-03-19 11:17:052008-03-19 11:17:056537758933589335893315
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
--------------------------------------------------------------------------------
< ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-al
gorithm="global"?>
589325893258932A2008-03-19 11:16:452008-03-19 11:16:452008-03-19 11:16:456536958932589325893210
.
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
--------------------------------------------------------------------------------
B
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
--------------------------------------------------------------------------------
< ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-al
gorithm="global"?>
589335893358933B2008-03-19 11:17:052008-03-19 11:17:052008-03-19 11:17:056537758933589335893315
< ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-al
gorithm="global"?>
589325893258932A2008-03-19 11:16:452008-03-19 11:16:452008-03-19 11:16:456536958932589325893210
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',
----------------------------------------------------------------------------------------------------
< ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?>
589355893558935C2008-03-19 15:08:472008-03-19 15:08:472008-03-19 15:08:4765385589355893522258935589352220EXTRA15008731200050
< ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?>
589325893258932A2008-03-19 11:16:452008-03-19 11:16:452008-03-19 11:16:4565369589325893211158932
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"?>11 589377589375SYSTEMPROC_0117PROCEDURE2008-03-19 16:39:342008-03-19 16:39:342008-03-19 16:39:34106655355589371018110procedure proc_01
5893720000as
5893730000begin
5893740000 null;
5893750000end;
SQL> select dbms_metadata.get_xml('PROCEDURE','PROC_02') from dual;
DBMS_METADATA.GET_XML('PROCEDURE','PROC_02')
--------------------------------------------------------------------------------
< ?xml version="1.0"?>11 589387589385SYSTEMPROC_0217PROCEDURE2008-03-19 16:39:342008-03-19 16:39:342008-03-19 16:39:34106655355589381018110procedure proc_02
5893820000as
5893830000begin
5893840000 NULL;
5893850000-- this is extra
5893860000end;
.
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
----------------------------------------------------------------------------------------------------
< ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?>
5893858938PROC_0258938procedure proc_02
589385893858938 NULL;
58938-- this is extra
5893860000end;
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…?
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…).
Bad Behavior has blocked 293 access attempts in the last 7 days.
This site uses cookies to ensure that it provides you the best experience on our website. If you continue to use this site we will assume that you are happy with it.OKRead more
Some useful extra info regarding XMLDIFF see OTN Forum thread discussion here:
https://forums.oracle.com/forums/thread.jspa?forumID=34&threadID=2339668
M.