Oracle | DBMS_METADATA

Oracle DBMS_METADATA_DIFF Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

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

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;

      

        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;

      

        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;

      

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

      

      exec dbms_metadata_diff.close(100001);

      

      PL/SQL procedure successfully completed.