Rem
Rem $Header: ecm_gendiff_pkgdef.sql 12-dec-2005.12:37:40 lappanmu Exp $
Rem
Rem ecm_gendiff_pkgdef.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_gendiff_pkgdef.sql
Rem
Rem    DESCRIPTION
Rem      Functions for retrieving View and Compare results for the ECM
Rem      generic snapshot viewer
Rem
Rem    NOTES
Rem      Added for use by the generic snapshot viewer in 10.2.0.2
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    lappanmu    12/11/05 - 
Rem    jochen      11/09/05 - getDiffs for generic comparison viewer
Rem    jochen      11/09/05 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100


CREATE OR REPLACE PACKAGE ecm_gendiff
AS

  s_result_unknown    CONSTANT CHAR(1) := 'U';
  s_result_different  CONSTANT CHAR(1) := 'D';
  s_result_same       CONSTANT CHAR(1) := 'S';

------------------------------------------------------------------------
-- FUNCTION get_child_diffs
--
-- In the UI, we are trying to display the following table with
-- comparison results for two targets.
--
--  keyname1     | keyname2     | key<n> |Descendant tables
--  -------------|--------------|--------|------------------
--  row1_keyval1 | row1_keyval2 | ...    |      =
--  -------------|--------------|--------|------------------
--  row2_keyval1 | row2_keyval2 | ...    |     !=
--  -------------|--------------|--------|------------------
--
-- This table shows rows of keys for an ECM table.  Assume that
-- there are two targets that have been compared.  For each row of
-- key values, we want to determine whether the table's descedant tables
-- have data values that are different in the comparison, or if the
-- descendant table values are all the same for this row of keys.
--
-- e.g. for a database configuration, the Tablespaces table has a child
-- table called Datafiles.  (In this example, the Tablespaces table only
-- has one key column and one child table, but this function handles any
-- number of key columns and descendant tables.)  This function would return
-- != and = values, meaning that for the two databases being compared,
-- the SYSTEM tablespace has different datafiles, and the SYSAUX tablespace
-- has the same datafiles.
--
--  MGMT_ECM_DB_TABLESPACES | MGMT_ECM_DB_DATAFILES |
--  ------------------------|-----------------------|
--  SYSTEM                  | !=                    |
--  ------------------------|-----------------------|
--  SYSAUX                  | =                     |
--  ------------------------|-----------------------|
--
-- PARAMETERS
--  p_compGuid    The delta_comp_guid
--  p_keys        A nested table.  Each table element contains a record
--                consisting of the the name of a key, and an array of key 
--                values for that key.  
--                For an array of key values, the values must be in the
--                same order as the arrays for other keys.
--  p_descs       Nested table of descendant table names
--
-- RETURNS
--  Array of results for the table and its descendants
--  The array is in the same order as the given key arrays
--
-------------------------------------------------------------------------
  FUNCTION get_child_diffs( p_compGuid    RAW,
                            p_keys        ecm_gendiff_keys_table,
                            p_descs       ecm_gendiff_tablename_table)
    RETURN ecm_gendiff_res_array;

--------------------------------------------------------------------------
-- Function: has_diffs
--
--  Quick way to determine if the comparison of the list of tables
--  given resulted in any different values.
--
--  Return 1 if the any of the tables in the given list have
--           any differences for the given comparision guid
--         0 if all comparison results for the given tables are the same
--------------------------------------------------------------------------
  FUNCTION has_diffs(p_compGuid RAW,
                     p_tables   ecm_gendiff_tablename_table)
    RETURN INTEGER;


--------------------------------------------------------------------------
-- Function: get_child_rowcounts
--
--  For each table in the list of given tables, determine how many
--  rows there are in each table for a given snapshot.
-- 
--  E.g. if the tables are MGMT_DB_DATAFILES_ECM and MGMT_DB_TABLES_ECM
--       this function would return the number of rows in 
--       MGMT_DB_DATAFILES_ECM for the given snapshot guid, and the number
--       of rows in MGMT_DB_TABLES_ECM for the snapshot guid
-- 
-- PARAMETERS
--    p_guid        The snapshot guid
--    p_tables      Nested table of table names
--
-- RETURN
--    Return ecm_gendiff_rowcounts_table
--         This is a nested table of records.  Each record consists of
--         a tablename and the rowcount for that table.
--
--------------------------------------------------------------------------
  FUNCTION get_child_rowcounts( p_guid    RAW,
                                p_tables  ecm_gendiff_tablename_table)
    RETURN ecm_gendiff_rowcounts_table;

END ecm_gendiff;
/
SHOW ERRORS;