Oracle DBMS_METADATA_DIFF
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose This package was undocumented in 11gR1 but now is included in the official documentation. The package is an API to dbms_metadata_int which compares the difference between two objects via their SXML formatted metadata.
AUTHID CURRENT_USER
Dependencies
DBMS_ASSERT DBMS_METADATA_INT KUPD$DATA
DBMS_CRYPTO DBMS_METADATA_UTIL PLITBLM
DBMS_LOB DBMS_SYS_ERROR XMLTYPE
DBMS_METADATA KU$_MULTI_DDLS  
Documented Yes
First Available 11.1.0.7
Security Model Owned by SYS with EXECUTE granted to PUBLIC

For some parts of this package, such as COMPARE_ALTER the Advisor privilege must be granted.
Source {ORACLE_HOME}/rdbms/admin/dbmsmet2.sql
Subprograms
 
ADD_DOCUMENT
Specifies an (S)XML document (as XMLTYPE) to be compared
Overload 1
dbms_metadata_diff.add_document(handle IN NUMBER, document IN sys.XMLType);
TBD
Overload 2 dbms_metadata_diff.add_document(handle IN NUMBER, document IN CLOB);
TBD
 
CLOSE
Cleans up context established by OPENC dbms_metadata_diff.close(handle IN NUMBER);
See OPENC Function Below
 
COMPARE_ALTER
This function compares the metadata for two objects and returns a set of ALTER statements for making object 1 like object2 dbms_metadata_diff.compare_alter(
object_type   IN VARCHAR2,
name1         IN VARCHAR2,
name2         IN VARCHAR2,
schema1       IN VARCHAR2 DEFAULT NULL,
schema2       IN VARCHAR2 DEFAULT NULL,
network_link1 IN VARCHAR2 DEFAULT NULL,
network_link2 IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
conn uwclass/uwclass@pdbdev

desc servers

desc serv_inst

set serveroutput on

DECLARE
 c CLOB;
BEGIN
  SELECT dbms_metadata_diff.compare_alter('TABLE', 'SERVERS', 'SERV_INST', USER, USER)
  INTO c
  FROM dual;

  dbms_output.put_line(c);
  dbms_advisor.create_file(c, 'CTEMP', 'compalter.txt');
END;
/
 
COMPARE_ALTER_XML
Compares the metadata for two objects and returns an ALTER_XML document dbms_metadata_diff.compare_alter_xml(
OBJECT_TYPE   IN VARCHAR2,
NAME1         IN VARCHAR2,
NAME2         IN VARCHAR2,
SCHEMA1       IN VARCHAR2,
SCHEMA2       IN VARCHAR2,
NETWORK_LINK1 IN VARCHAR2,
NETWORK_LINK2 IN VARCHAR2)
RETURN CLOB;
conn sys@pdbdev as sysdba

set serveroutput on

DECLARE
 c CLOB;
BEGIN
  SELECT dbms_metadata_diff.compare_alter_xml('TABLE', 'EMP', 'EMPLOYEES', 'SCOTT', 'HR')
  INTO c
  FROM dual;

  dbms_output.put_line(c);
END;
/
 
COMPARE_SXML
The functions compares the metadata for two objects and returns an sxml difference document dbms_metadata_diff.compare_sxml(
object_type   IN VARCHAR2,
name1         IN VARCHAR2,
name2         IN VARCHAR2,
schema1       IN VARCHAR2 DEFAULT NULL,
schema2       IN VARCHAR2 DEFAULT NULL,
network_link1 IN VARCHAR2 DEFAULT NULL,
network_link2 IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
conn sys@pdbdev as sysdba

set serveroutput on

DECLARE
 c CLOB;
BEGIN
  SELECT dbms_metadata_diff.compare_sxml('TABLE', 'EMP', 'EMPLOYEES', 'SCOTT', 'HR')
  INTO c
  FROM dual;

  dbms_output.put_line(c);
END;
/
 
FETCH_CLOB
Fetches an SXML diff document

Overload 1
dbms_metadata_diff.fetch_clob(handle IN NUMBER) RETURN CLOB;
TBD
Overload 2 dbms_metadata_diff.fetch_clob(
handle IN     NUMBER,
xmldoc IN OUT CLOB);
TBD
Overload 3 dbms_metadata_diff.fetch_clob(
handle IN     NUMBER,
xmldoc IN OUT CLOB,
diffs     OUT BOOLEAN);
TBD
 
OPENC
Establishes a 'compare' context and specifies the object type for comparing to (S)XML documents dbms_metadata_diff.openc(object_type IN VARCHAR2) RETURN NUMBER;
SELECT dbms_metadata_diff.openc('TABLE')
FROM dual;

exec dbms_metadata_diff.close(100001);

Related Topics
DBMS_METADATA
DBMS_METADATA_BUILD
DBMS_METADATA_DPBUILD
DBMS_METADATA_HACK
DBMS_METADATA_INT
DBMS_METADATA_UTIL
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved