Oracle DBMS_METADATA_DIFF
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose This package was undocumented in 11gR1 but now is included in the online 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 KU$_MULTI_DDLS
DBMS_CRYPTO DBMS_METADATA_UTIL KUPD$DATA
DBMS_LOB DBMS_STANDARD PLITBLM
DBMS_METADATA DBMS_SYS_ERROR XMLTYPE
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
Built-in Functions
Built-in Packages
DBMS_METADATA
DBMS_METADATA_BUILD
DBMS_METADATA_DPBUILD
DBMS_METADATA_HACK
DBMS_METADATA_INT
DBMS_METADATA_UTIL
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved