88 DBMS_METADATA_DIFF

Using DBMS_METADATA_DIFF

This section contains topics which relate to using the DBMS_METADATA_DIFF package.

• Overview

• Security Model

Overview

You can use the interfaces contained in the DBMS_METADATA_DIFF package to compare 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.

Security Model

The browsing interface of the DBMS_METADATA_DIFF package actually uses the DBMS_METADATA package to fetch the metadata to be compared. Therefore, the security model used for DBMS_METADATA also applies to DBMS_METADATA_DIFF. (Note, however, that DBMS_METADATA_DIFF does not support all object types.)

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

Return Values

DBMS_METADATA_DIFF.COMPARE_xxx returns the differences between two objects.

Exceptions

• INVALID_ARGVAL

  A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

• OBJECT_NOT_FOUND

  The specified object was not found in the database.

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:

• COMPARE_SXML returns an SXML difference document.

• COMPARE_ALTER returns a set of ALTER statements for making the first object like the second object.

• COMPARE_ALTER_XML returns an ALTER_XML document.

Summary of DBMS_METADATA_DIFF Subprograms

The DBMS_METADATA_DIFF subprograms are used to:

• Specify the type of objects to be compared

• Specify the SXML documents to be compared

• Show the differences between the compared documents

• Clean up after the comparison

Table 88-2 provides a summary of DBMS_METADATA_DIFF subprograms.

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

Return Values

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

Exceptions

• INVALID_ARGVAL

  A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

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

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

• INVALID_ARGVAL

  A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

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

Return Values

The differences between documents 1 and 2.

Exceptions

• INVALID_ARGVAL

  A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

CLOSE Procedure

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

Syntax

DBMS_METADATA_DIFF.CLOSE(
handle IN NUMBER);

Parameters

Exceptions

• INVALID_ARGVAL

  A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.