Rem drv: <migrate type="schema_upgrade" version="10.2.0.2"/>
Rem
Rem $Header: ecm_schema_upgrade.sql 12-dec-2005.12:37:38 lappanmu Exp $
Rem
Rem ecm_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_schema_upgrade.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    lappanmu    12/12/05 - Upgrade for generic viewer 
Rem    lappanmu    12/12/05 - Upgrade for generic viewer 
Rem    jochen      12/09/05 - 10.2 patchset ecm schema upgrades
Rem                           for generic hierarchical snapshot viewer
Rem    jochen      12/09/05 - Created
Rem

DEFINE ecm_gendiff_row_count  = 128;

rem
rem Drop all the types in the right order
rem
BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE ecm_gendiff_compkeys_table ' ;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -04043 THEN
      NULL;
    ELSE
      Raise;
    END IF;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE ecm_gendiff_keys_table ' ;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -04043 THEN
      NULL;
    ELSE
      Raise;
    END IF;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE ecm_gendiff_rowcounts_table ' ;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -04043 THEN
      NULL;
    ELSE
      Raise;
    END IF;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE ecm_gendiff_keyvalues_rec ' ;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -04043 THEN
      NULL;
    ELSE
      Raise;
    END IF;
END;
/
rem
rem ecm_gendiff_compkeys_rec
rem PURPOSE
rem   This object represents a list of key values for a specific key
rem
CREATE OR REPLACE TYPE ecm_gendiff_compkeys_rec AS OBJECT(
  keyname VARCHAR2(64),
  keyval  VARCHAR2(4000)
  );
/
rem
rem ecm_gendiff_compkeys_table
rem PURPOSE
rem   Nested table of keyname/value objects
rem
CREATE OR REPLACE TYPE ecm_gendiff_compkeys_table
  AS TABLE OF ecm_gendiff_compkeys_rec;
/
rem
rem PURPOSE
rem   This object represents a list of key values for a specific key
rem
CREATE OR REPLACE TYPE ecm_gendiff_keyvalue_array
  AS VARRAY(&ecm_gendiff_row_count) OF VARCHAR2(4000);
/
rem
rem PURPOSE
rem   This object represents a key and a list of values for this key.
rem
rem  COLUMNS
rem   keyname The name of the key column
rem   vals    An array of key values in the order that they appear in
rem           the UI
rem
CREATE OR REPLACE TYPE ecm_gendiff_keyvalues_rec AS
OBJECT (keyname VARCHAR2(64),
        vals    ecm_gendiff_keyvalue_array);
/
rem
rem PURPOSE
rem   This object is a list of keys and their associated values
rem
CREATE OR REPLACE TYPE ecm_gendiff_keys_table
  AS TABLE OF ecm_gendiff_keyvalues_rec;
/
rem
rem PURPOSE
rem   This object represents a list of table names
rem
CREATE OR REPLACE TYPE ecm_gendiff_tablename_table AS
    TABLE OF VARCHAR2(30);
/
/
rem
rem PURPOSE
rem   This object is a list of comparison results
rem   Each value is either 0 for unknown, 1 for different, 2 for same
rem
CREATE OR REPLACE TYPE ecm_gendiff_res_array
  AS VARRAY(&ecm_gendiff_row_count) OF CHAR(1);
/
rem
rem PURPOSE
rem   This object is the rowcount for a child table
rem COLUMNS
rem   table_name  Name of the child table
rem   count       The number of rows
rem
CREATE OR REPLACE TYPE ecm_gendiff_rowcount_rec AS OBJECT
(
  table_name  VARCHAR2(30),
  count       INTEGER
);
/
rem
rem PURPOSE
rem   This object is a bag of rowcounts for a set of child tables
rem
CREATE OR REPLACE TYPE ecm_gendiff_rowcounts_table
  AS TABLE OF ecm_gendiff_rowcount_rec;
/