Rem
Rem $Header: dbmsmet2.sql 18-apr-2008.11:29:11 lbarton Exp $
Rem
Rem dbmsmet2.sql
Rem
Rem Copyright (c) 2008, Oracle.  All rights reserved.  
Rem
Rem    NAME
Rem      dbmsmet2.sql - Package header for DBMS_METADATA_DIFF
Rem       (moved from dbmsmeta.sql)
Rem
Rem    DESCRIPTION
Rem      This file contains the interface to the Metadata API
Rem      comparison interface.
Rem
Rem      THIS PACKAGE IS UNDOCUMENTED.  USE IS CURRENTLY RESTRICTED
Rem      TO ORACLE INTERNAL USERS ONLY.
Rem    
Rem      USE OF THIS PACKAGE REQUIRES A LICENSE TO THE ORACLE ENTERPRISE
Rem      MANAGER CHANGE MANAGEMENT OPTION.
Rem
Rem    PUBLIC FUNCTIONS / PROCEDURES
Rem     OPENC           - Establish context for comparing 2 (S)XML docs. 
Rem     ADD_DOCUMENT    - Specify an SXML document to be compared
Rem     FETCH_CLOB      - Fetch SXML diff document.
Rem     CLOSE           - Cleanup context established by OPENC.
Rem       (the browsing interface)
Rem     COMPARE_ALTER   - returns a set of ALTER statements for making 
Rem                       object 1 like object 2.
Rem     COMPARE_ALTER_XML
Rem                     - returns an ALTER_XML document
Rem     COMPARE_SXML    - returns a SXML difference document
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    lbarton     04/14/08 - Created
Rem

CREATE OR REPLACE PACKAGE dbms_metadata_diff AUTHID CURRENT_USER AS 
---------------------------------------------------------------------
-- Overview
-- This pkg implements the comparison interface of the Data Pump Metadata API.
--
--      THIS PACKAGE IS UNDOCUMENTED.  USE IS CURRENTLY RESTRICTED
--      TO ORACLE INTERNAL USERS ONLY.
--    
--      USE OF THIS PACKAGE REQUIRES A LICENSE TO THE ORACLE ENTERPRISE
--      MANAGER CHANGE MANAGEMENT OPTION.
---------------------------------------------------------------------
-- SECURITY
-- This package is owned by SYS with execute access granted to PUBLIC.
-- It runs with invokers rights, i.e., with the security profile of
-- the caller.  It calls DBMS_METADATA_INT to perform privileged
-- functions.

---------------------------
-- PROCEDURES AND FUNCTIONS
--
-- OPENC: 
--        This function establishes a 'compare' context and specifies the 
--        object type for comparing to (S)XML documents.
-- PARAMETERS:
--      object_type  - Identifies the type of objects to be compared; i.e.,
--              TABLE, INDEX, etc. 
-- RETURNS:
--      A handle to be used in subsequent calls to ADD_DOCUMENT,
--      COMPARE_SXML, etc
-- EXCEPTIONS:
--      INVALID_ARGVAL  - a NULL or invalid value was supplied for an input
--              parameter.

  FUNCTION openc (
                object_type     IN  VARCHAR2)
        RETURN NUMBER;

-- ADD_DOCUMENT : Specifies an (S)XML document (as XMLTYPE) to be compared.
-- PARAMETERS:
--      handle          - Context handle from previous OPENC call.
--      document        - document (xmltype) to be compared
-- EXCEPTIONS:
--      INVALID_ARGVAL  - a NULL or invalid value was supplied for an input
--              parameter.

  PROCEDURE add_document (
               handle          IN  NUMBER,
               document        IN  sys.XMLType);

-- ADD_DOCUMENT : Specifies an (S)XML document (as clob) to be compared.
-- PARAMETERS:connect
--      handle          - Context handle from previous OPENC call.
--      document        - document (clob) to be compared
-- EXCEPTIONS:
--      INVALID_ARGVAL  - a NULL or invalid value was supplied for an input
--              parameter.

  PROCEDURE add_document (
                handle          IN  NUMBER,
                document        IN  CLOB);

-- PROCEDURE FETCH_CLOB: Return SXML diff document.
-- PARAMETERS:	handle- (IN) Context handle from previous OPENC call.
--              xmldoc - (IN OUT) previously allocated CLOB to hold the
--                       returned diff document.
--		diffs  - (OUT) flag (1 == diffs found; 0==no diffs found)

  FUNCTION fetch_clob (
		handle	IN NUMBER)
         RETURN CLOB;

  PROCEDURE fetch_clob (
		handle	IN NUMBER,
		xmldoc	IN OUT NOCOPY CLOB);

  PROCEDURE fetch_clob (
		handle	IN NUMBER,
		xmldoc	IN OUT NOCOPY CLOB,
                diffs   OUT BOOLEAN);

-- CLOSE:       Cleanup all context associated with handle.
-- PARAMETERS:  handle  - Context handle from previous OPENC call.

  PROCEDURE CLOSE (handle IN NUMBER);

-- COMPARE_SXML:
--        The functions compares the metadata for two objects and returns
--        an sxml difference document.
-- RETURNS:     
--        CLOB containing sxml difference document.
-- PARAMETERS:  
--        object_type   - type of object to be retrieved and compared
--        name1         - first object to be compared
--        name2         - second object to be compared
--        schema1       - schema of the first obj to be compared
--        schema2       - schema of the second obj to be compared
--        network_link1 - name of a database link where the first obj
--                        resides.
--        network_link2 - name of a database link where the second obj
--                        resides.
-- EXCEPTIONS:  Throws an exception if COMPARE failed.

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

-- COMPARE_ALTER:
--        This function compares the metadata for two objects and returns a
--        set of ALTER statements for making object 1 like object2.
-- RETURNS:     
--        CLOB containing alter statements for making object 1 like object 2
-- PARAMETERS:  
--        object_type   - type of object to be retrieved and compared
--        name1         - first object to be compared
--        name2         - second object to be compared
--        schema1       - schema of the first obj to be compared
--        schema2       - schema of the second obj to be compared
--        network_link1 - name of a database link where the first obj
--                        resides.
--        network_link2 - name of a database link where the second obj
--                        resides.
-- EXCEPTIONS:  Throws an exception if COMPARE failed.

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

-- COMPARE_ALTER_XML:
--        This function compares the metadata for two objects and returns
--        an ALTER_XML document.
-- RETURNS:     
--        CLOB containing sxml difference document.
-- PARAMETERS:
--        object_type   - type of object to be retrieved and compared
--        name1         - first object to be compared
--        name2         - second object to be compared
--        schema1       - schema of the first obj to be compared
--        schema2       - schema of the second obj to be compared
--        network_link1 - name of a database link where the first obj
--                        resides.
--        network_link2 - name of a database link where the second obj
--                        resides.
-- EXCEPTIONS:  Throws an exception if COMPARE failed.

  FUNCTION 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;
END DBMS_METADATA_DIFF;
/
GRANT EXECUTE ON sys.dbms_metadata_diff TO PUBLIC; 
CREATE OR REPLACE PUBLIC SYNONYM dbms_metadata_diff FOR sys.dbms_metadata_diff;