Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)

Part Number E10577-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

85 DBMS_METADATA_DIFF

The DBMS_METADATA_DIFF package contains the interfaces for comparing two metadata documents in SXML format. The result of the comparison is an SXML difference document. This document can be converted to other formats using the DBMS_METADATA submit interface and the CONVERT API.

Use of the DBMS_METADATA_DIFF package requires a license to the Oracle Enterprise Manager Change Management option.

See Also:

Oracle Database Utilities for more information and for examples of using the Metadata API

This chapter contains the following topics:


Browsing APIs for Fetching and Comparing Objects

These functions allow you to compare the metadata for two objects with a single call.

Syntax

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;

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;

DBMS_METADATA_DIFF.COMPARE_ALTER_XML(
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;

Parameters

Table 85-1 COMPARE_xxx Function Parameters

Parameters Description

object_type

The type of object to be compared. Valid type names are CLUSTER, CONTEXT, DB_LINK, FGA_POLICY, INDEX, MATERIALIZED_VIEW, MATERIALIZED_VIEW_LOG, QUEUE, QUEUE_TABLE, RLS_CONTEXT, RLS_GROUP, RLS_POLICY, ROLE, SEQUENCE, SYNONYM, TABLE, TABLESPACE, TRIGGER, TYPE, TYPE_SPEC, TYPE_BODY, USER, and VIEW.

name1

The name of the first object in the comparison.

name2

The name of the second object in the comparison.

schema1

The schema of the first object in the comparison. The default is the current user.

schema2

The schema of the second object in the comparison. The default is the value of schema1.

network_link1

The name of a database link to the database on which the first object resides. If NULL (the default), then the object is assumed to be in the database on which the caller is running.

network_link2

The name of a database link to the database on which the second object resides. The default is the value of network_link1.


Return Values

DBMS_METADATA_DIFF.COMPARE_xxx returns the differences between two objects.

Exceptions

Usage Notes

These functions encapsulate calls to both DBMS_METADATA and DBMS_METADATA_DIFF functions and procedures to fetch the metadata for each of the two objects and compare them.

Which function you use depends on the comparison format you want:


Summary of DBMS_METADATA_DIFF Subprograms

The DBMS_METADATA_DIFF subprograms are used to:

Table 85-2 provides a summary of DBMS_METADATA_DIFF subprograms.

Table 85-2 DBMS_METADATA_DIFF Package Subprograms

Subprogram Description

OPENC Function

Specifies the type of objects to be compared

ADD_DOCUMENT Procedure

Specifies an SXML document to be compared

FETCH_CLOB Functions and Procedures

Returns a CLOB showing the differences between the two documents specified by ADD_DOCUMENT

CLOSE Procedure

Invalidates the handle returned by OPENC and cleans up associated state



OPENC Function

This function specifies the type of objects to be compared. The return value is an opaque context handle.

Syntax

DBMS_METADATA_DIFF.OPENC (
object_type  IN VARCHAR2)
RETURN NUMBER;

Parameters

Table 85-3 OPENC Function Parameters

Parameters Description

object_type

The type of object to be compared. Valid type names are CLUSTER, CONTEXT, DB_LINK, FGA_POLICY, INDEX, MATERIALIZED_VIEW, MATERIALIZED_VIEW_LOG, QUEUE, QUEUE_TABLE, RLS_CONTEXT, RLS_GROUP, RLS_POLICY, ROLE, SEQUENCE, SYNONYM, TABLE, TABLESPACE, TRIGGER, TYPE, TYPE_SPEC, TYPE_BODY, USER, and VIEW.


Return Values

The opaque handle that is returned is used as input to ADD_DOCUMENT, FETCH_xxx and CLOSE.

Exceptions


ADD_DOCUMENT Procedure

This procedure specifies an SXML document to be compared.

Syntax

DBMS_METADATA_DIFF.ADD_DOCUMENT(
handle IN NUMBER, document IN sys.XMLType);

DBMS_METADATA_DIFF.ADD_DOCUMENT(
handle IN NUMBER, document IN CLOB);

Parameters

Table 85-4 CLOSE Procedure Parameters

Parameter Description

handle

The handle returned from OPENC

document

A document to be compared. The document must be of the type specified in OPENC.


Usage Notes

Because the comparison interface allows you to compare exactly two SXML documents, a program must call ADD_DOCUMENT exactly twice for each OPENC handle. In the comparison result, the document specified by the first call is document 1, and the document specified by the second call is document 2.

Exceptions


FETCH_CLOB Functions and Procedures

The FETCH_CLOB functions and procedures return a CLOB showing the differences between the two documents specified by ADD_DOCUMENT.

Syntax

DBMS_METADATA_DIFF.FETCH_CLOB(
handle IN NUMBER)
RETURN CLOB;

DBMS_METADATA_DIFF.FETCH_CLOB(
handle IN  NUMBER, 
doc    IN  OUT NOCOPY CLOB);

DBMS_METADATA_DIFF.FETCH_CLOB(
handle IN  NUMBER, 
doc    IN  OUT NOCOPY CLOB 
diffs  OUT BOOLEAN);

Parameters

Table 85-5 CONVERT Subprogram Parameters

Parameter Description

handle

The handle returned from OPENC.

doc

A CLOB containing the differences between documents 1 and 2.

diffs

TRUE if the documents are different or FALSE if they are identical.


Return Values

The differences between documents 1 and 2.

Exceptions


CLOSE Procedure

This procedure invalidates the handle returned by OPENC and cleans up associated state.

Syntax

DBMS_METADATA_DIFF.CLOSE(
handle IN NUMBER);

Parameters

Table 85-6 FETCH_xxx Function Parameters

Parameters Description

handle

The handle returned from OPENC


Exceptions