Oracle | DBMS_METADATA

Oracle DBMS_METADATA_DIFF Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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	KUPD$DATA
DBMS_CRYPTO	DBMS_METADATA_UTIL	KUPU$UTILITIES_INT
DBMS_DATAPUMP	DBMS_STANDARD	PLITBLM
DBMS_LOB	DBMS_SYS_ERROR	XMLTYPE
DBMS_METADATA	KU$_MULTI_DDLS

Documented Yes: Packages and Types Reference

First Available 11.1

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	COMPARE_ALTER_XML	FETCH_CLOB
CLOSE	COMPARE_SXML	OPENC
COMPARE_ALTER

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

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