Rem
Rem  Copyright (c) 1997, 1998, 1999 by Oracle Corporation 
Rem
Rem    NAME
Rem      r0703040.sql: Post-catrep Advanced Replication upgrade
Rem                    from 7.3.4 to the new release
Rem
Rem    DESCRIPTION
Rem
Rem      Post-catrep Advanced Replication upgrade from 7.3.4 to
Rem      the new release.
Rem
Rem      This script upgrades Advanced replication-specific packages/views
Rem      from 7.3.4 to the new release in the following stages:
Rem        STAGE 1: post-catrep upgrade from 7.3.4 to 8.0.2
Rem        STAGE 2: post-catrep upgrade from 8.0.2 to 8.0.3
Rem        STAGE 3: post-catrep upgrade from 8.0.3 to the new release
Rem                 [calls r0800030]
Rem
Rem    NOTES:
Rem
Rem      * Please read the Oracle Migration book and the README notes for
Rem        details.  This script is not meant to be run independently.
Rem
Rem      * It expects CATALOG, CATPROC and CATREP to be loaded.
Rem
Rem      * It must be run under SYS or INTERNAL.
Rem
Rem      * It performs post-catrep upgrade cleanup
Rem
Rem 
Rem    MODIFIED   (MM/DD/YY)
Rem    ncramesh    08/04/98 - change for sqlplus
Rem    usundara    06/03/98 - rework upgrade/downgrade/migration scripts:
Rem                           * change name catrep8m.sql -> r0703040.sql
Rem                           * remove call to catrep.sql
Rem                           * STAGE 1: copy contents of old cat8000r.sql
Rem                           * STAGE 2: copy contents of old cat8003r.sql
Rem                           * STAGE 3: call r0800030.sql
Rem     liwong     05/06/97 -  Created
Rem

Rem ===========================================================================
Rem BEGIN STAGE 1: post-catrep upgrade from 7.3.4 to 8.0.2
Rem ===========================================================================


Rem  This was pulled out of the old cat8000r.sql.
Rem  Here's the old modification history for that file.
Rem
Rem     liwong     05/06/97 -  Remove update to master_version
Rem     liwong     03/19/97 -  Remove "add constraint def$_calldest_call"
Rem     liwong     12/24/96 -  Moved alter mlog$ tables to cat8000s.sql
Rem     liwong     11/26/96 -  Created 
Rem
Rem      This upgrade script does NOT assume any upgrade order. One can
Rem      upgrade the masterdef, master and snapshot in any order. In fact, 
Rem      we can't force any order because we can have two databases and two
Rem      repgroups, one database is the mastardef of repgroup 1 and the other
Rem      database is the masterdef of repgroup 2. There is no strict order at
Rem      all in this situation. 
Rem
Rem      Note that, if a snapshot site gets upgraded, it may assume its 
Rem      master as V7 though its master may be upgraded at a later time.
Rem
Rem      Some post-upgrade steps are needed in order to do further
Rem      replication activities.
Rem
Rem      This script is re-runnable and guaranteed to run successfully to
Rem      completion with no remote database available.
Rem

-----------------------------------------------------------------------------
-- What needs to be done before loading this script:
-- o. make sure all pending changes in deferred transaction queues applied.
-- o. make sure master snapshot logs and updatable snapshot logs are empty.
--    by doing appropriate replication admin and/or refresh.
-- o. appropriate database links for sys have been created (since this script 
--    is run under sys), such database links should be dropped after 
--    migration.
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
-- What needs to be done at each site
-- I. Common upgrade for all sites:
--    o. alter table, drop and add constraints (done in cat8000.sql).
--    o. populate system.recat$_repcolumn.
--    o. drop REP$WHAT_AM_I package and its body.
--    o. drop $RT triggers for appropriate tables.
--
-- II.Site specific information:
--    A. masterdef/master site:
--       o. truncate snapshot logs.
--       o. drop $TP package and its body.
--       o. drop $ST triggers.
--       o. create internal triggers for replication and snapshot logs.
--
--
--    B. snapshot site:
--       o. create internal triggers for replication and updatable snapshots.
--       o. drop mview$[snapshotname] in snap$.mview$.
-----------------------------------------------------------------------------


-----------------------------------------------------------------------------
-- Some conversion from V7 to V8, which can not be (are not) done in this 
-- script:
--
-- o. does not automatically upgrade rowid snapshots to 
--    primary key snapshots, because:
--    1. The queries needed to determine whether a rowid snapshot is 
--       upgradable are complex and requires the master site to be available.
--    2. Users may not want all of their rowid snapshots to be automatically
--       upgraded.
--
-- o. does not automatically convert V7 subquery snapshots into fast-refresh
--    snapshots because their underlying objects are incompatible.
--
-- o. does not update the owner of the generated objects (e.g., packages) to
--    propagator because we may not know the propagator's name during upgrade.
--
-- o. for $RP, there are two versions of <table>$RP.rep_update, one is like
--    usual V7 rep_update, the additional one contains an additional column:
--    column_changed$, which is used for communication minization.
--    In this upgrade, the additional rep_update will not be generated,
--    otherwise, we need to drop the $RP packages and then regenerate them 
--    (doing so requires all sites be available during upgrade).
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
-- Steps to convert ROWID snapshot to primary key snapshot:
--   1. Make sure master table has primary key.
--   2. Lock snapshot table and ensure the updatable snapshot log is empty
--      for updatable snapshots (the locking will be done automatically
--      when you perform the SQLs in step 3 and 4).
--   3. At master site: 
--         alter snapshot log on <master table> add primary key
--      (this will add primary key columns to snapshot log).
--   4. At snapshot site: 
--         alter snaphot <snapshotname> refresh with primary key 
--      (this will add primary key to the underlying updatable snapshot logs).
--
--   Note: 
--      o. master site should be available during snapshot conversion
--      o. triggers are not required to be regenerated because they
--         are internalized
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
-- Steps to conform to new security model 
--   There are two parts to the post-upgrade steps. The steps in the first
-- part are required to get things working. The steps in the second part
-- are required to remove obsolete objects (and close security loopholes)
-----------------------------------------------------------------------------
-----------------------------------
-- Part I:
--   1. For a master site:
--      a). Grant the required privileges to existing repadmin using 
--          grant_admin_any_schema() or grant_admin_schema().
--      b). Create a propagator for the site. Use 
--          dbms_defer_sys.register_propagator() to register the propagator
--          for the site.
--      c). In the schema of the propagator, create a database link connecting
--          to its receiver at every other master site.
--      d). Create a receiver for trusted sites. Grant 
--          EXECUTE ANY PROCEDURE privilege to the receiver.
--      e). Create a site-specific receiver for each untrusted site. Grant
--          to the receiver execute privilege on dbms_defer_internal_sys.
--          o. If it is for an untrusted master site, also grant to the receiver
--             execute privileges on $RPs and wrapped procedures associated
--             with replicated objects from the untrusted master site. 
--          o. If it is for an untrusted snapshot site, also grant to the
--             receiver execute privileges on $RPs and wrappers associated
--             with snapshot objects from the untrusted snapshot site
--      f). Suspend master activity for all object groups from their 
--          respective master definition sites
--      g). Generate replication support for all replicated objects from
--          their respective master definition sites. All generated wrappers
--          will be in the schema of the propagator
--      h). Resume master activity for all object groups from their respective
--          master definition sites.
--
--   2. For a snapshot site:
--      a). Grant the required privileges to existing repadmin using 
--          grant_admin_any_schema() or grant_admin_schema().
--      b). Create a propagator for the site. Use 
--          dbms_defer_sys.register_propagator() to register the progagator for
--          the site.
--      c). In the schema of the propagator, create a database link connecting
--          to its receiver at the snapshot master site.
--      d). Generate snapshot support for all snapshot objects.
-----------------------------------

-----------------------------------
-- Part II:
--   1. For a master site:
--      a). Obsolete the surrogate user. Revoke from the surrogate user
--          privileges that were granted by grant_surrogate_repcat().
--          Optionally, drop the surrogate user.
--      b). Drop the database links that were required only for propagating 
--          deferred replication RPCs.
--      c). Re-grant privileges to existing repadmin. First, revoke from
--          existing repadmin privileges that were granted by 
--          grant_admin_any_repgroup() or grant_admin_repgroup(), and then
--          grant privileges to the same repadmin using
--          grant_admin_any_schema() or grant_admin_scheam().
--
--   2. For a snapshot site:
--      a). Re-grant privileges to existing repadmin. First, revoke from
--          existing repadmin privileges that were granted by 
--          grant_admin_any_repgroup() or grant_admin_repgroup(), and then
--          grant privileges to the same repadmin using
--          grant_admin_any_schema() or grant_admin)scheam().
-----------------------------------

Rem =======================================================================
Rem               specific upgrade for master (masterdef)
Rem =======================================================================

-------------------------------------------------------------------------
--   Case: drop REP$WHAT_AM_I package and its body because they are no
-- longer needed in V8
-------------------------------------------------------------------------
DECLARE
  sql_cursor   NUMBER;
  rows         NUMBER;

  CURSOR pkgs IS
    SELECT sname, oname, DECODE(type, 9, 'PACKAGE', 11, 'PACKAGE BODY') ntype,
           type
    FROM system.repcat$_repobject
    WHERE oname = 'REP$WHAT_AM_I'
       AND type IN (9, 11)
    ORDER BY sname, type DESC;  -- drop package body first, then drop its spec

BEGIN
  sql_cursor := DBMS_SQL.OPEN_CURSOR;

  FOR rec IN pkgs LOOP
    BEGIN
      IF DBMS_REPCAT_UTL.OBJECT_EXISTS(rec.sname, rec.oname, rec.ntype) THEN
        -- Here, we can not use dbms_repcat_utl.drop_an_object because 
        -- it needs to get propagator name and propagator may NOT even exist
        -- at this point
        DBMS_SQL.PARSE(sql_cursor,    
          'DROP ' || rec.ntype || ' "' || rec.sname || '"."' || 
          rec.oname || '"',
          DBMS_SQL.NATIVE);
        rows := DBMS_SQL.EXECUTE(sql_cursor);
      END IF;

      --- Delete from RepCat if there is no exception or the object not exists
      DELETE FROM system.repcat$_repobject
        WHERE sname = rec.sname AND oname = rec.oname AND type = rec.type;
      COMMIT;

    EXCEPTION 
      WHEN others THEN
          DBMS_SYSTEM.KSDWRT(2, '  Error ' || TO_CHAR(SQLCODE) || ': ' || 
                             SQLERRM);
          DBMS_SYSTEM.KSDWRT(2, '  Error in dropping ' || rec.ntype || ' "' || 
                                rec.sname || '"."' || rec.oname || '"');
    END;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(sql_cursor); 
  COMMIT;
END;
/



Rem =======================================================================
Rem                   specific upgrade for snapshot
Rem =======================================================================
-------------------------------------------------------------------------
-- Note: 
--     Some snapshot objects which are not under replication are upgraded
--   in the file: cat8000s.sql. Some examples are: triggers for updatable
--   snapshot log, master snapshot log and dropping of mview.
-------------------------------------------------------------------------

Rem =======================================================================
Rem           common upgrade for master(masterdef) and snapshot
Rem =======================================================================

-----------------------------------------------------------------------------
--   Case: populate system.repcat$_repcolumn for replicated tables and 
-- updatable snapshot
-----------------------------------------------------------------------------
--   Since repcat$_repcolumn is a new table, we need to populate it during
-- database migration: 
--      o. Masterdef site will create the repcolumns locally by calling 
--         create_repcolumn
--      o. Master site will call get_repcolumn_info to get replicated columns 
--         from masterdef site. If masterdef site can not be reached, the
--         repcolumns will be created locally.
--      o. Snapshot site will call get_repcolumn_info to get replicated columns
--         from its master site which may in turn call get_repcolumn_info to
--         get the information from the masterdef site. If master site can 
--         not be reached, the repcolumns will be created locally.
--
--   If master/snapshot gets zero repcolumn from masterdef/master, the
-- repcolumn will be created locally anyway because we can have the following
-- scenario:
--   there are appropriate dblinks for sys in master and masterdef sites, 
-- and master gets upgraded first, and it gets zero repcolumn from the 
-- masterdef. All the later replication activities (insert/update/delete) 
-- won't get propagated correctly from the master to the masterdef.
--
--
DECLARE 
  my_dblink      VARCHAR2(128);
  v_masterdef    BOOLEAN;

  CURSOR table_snapshots IS
    SELECT DISTINCT ro.sname, ro.oname, ro.type, rs.master, rs.masterdef,
                    rs.dblink
    FROM system.repcat$_repobject ro,  -- ro provides both sname and gname
         system.repcat$_generated g,
         system.repcat$_repschema rs
    WHERE 
      ((ro.type = 2 AND rs.masterdef = 'Y') OR              -- tables
       (ro.type = -1 AND rs.snapmaster = 'Y')) AND          -- snapshots
      ro.sname = g.base_sname AND           --   make sure replication support
      ro.oname = g.base_oname AND           -- has been generated
      ro.type = g.base_type AND
      ro.gname = rs.sname;      -- make sure they are in the same object group

  -------------------------------------------------------------------------
  -- definition of the auxiliary procedure
  -------------------------------------------------------------------------
  PROCEDURE get_remote_repcolumn(recsname IN VARCHAR2, reconame IN VARCHAR2,
                                 rectype  IN NUMBER,   ms_dblink IN VARCHAR2) IS
    rep_version   NUMBER;
    repcolumn_cnt NUMBER;

  BEGIN
    -- if site ms_dblink is not available, exception will be raised and
    -- the repcat$_repcolumn will be created locally
    rep_version := DBMS_REPCAT_UTL.REPLICATION_VERSION(ms_dblink);
    DBMS_REPCAT_UTL.GET_REPCOLUMN_INFO(rep_version, ms_dblink,
                                       recsname, reconame, rectype);
    SELECT count(*) INTO repcolumn_cnt
      FROM SYSTEM.REPCAT$_REPCOLUMN
      WHERE sname = recsname AND
            oname = reconame AND
            type  = rectype;

    -- create repcolumn locally
    IF repcolumn_cnt = 0 THEN
      DBMS_REPCAT_UTL.CREATE_REPCOLUMN(recsname, reconame, rectype);
    END IF;
    COMMIT;

    EXCEPTION         
      -- populate the column locally upon exceptions
      WHEN others THEN
        DBMS_SYSTEM.KSDWRT(2, '  Failed to get repcolumn info from "'||
                           ms_dblink || '" for "' || recsname || '"."'
                        || reconame || '", repcolumn is created locally');
        DBMS_REPCAT_UTL.CREATE_REPCOLUMN(recsname, reconame, rectype);
        COMMIT;
  END;
  -------------------------------------------------------------------------
  --  end of the auxiliary procedure definition
  -------------------------------------------------------------------------
BEGIN
  SELECT global_name INTO my_dblink FROM global_name;

  FOR rec IN table_snapshots LOOP
    BEGIN
      -- it also takes care of database links with connection qualifiers 
      v_masterdef := rec.masterdef = 'Y' AND 
                (rec.dblink = my_dblink OR rec.dblink LIKE my_dblink || '@%');

      IF rec.master = 'Y' THEN 
        -- master and masterdef
        IF v_masterdef = TRUE THEN
          -- masterdef
          DBMS_REPCAT_UTL.CREATE_REPCOLUMN(rec.sname, rec.oname, rec.type);
        ELSE
          -- master site
          get_remote_repcolumn(rec.sname, rec.oname, rec.type, rec.dblink);
        END IF;
      ELSE
        -- snapshot site
        get_remote_repcolumn(rec.sname, rec.oname, rec.type, rec.dblink);
      END IF;
      COMMIT;

    EXCEPTION
      WHEN others THEN
        DBMS_SYSTEM.KSDWRT(2, '  Error ' || TO_CHAR(SQLCODE) || ': ' || 
                           SQLERRM);
        DBMS_SYSTEM.KSDWRT(2, '  Error in populating repcolumn for "' ||
                              rec.sname || '"."' || rec.oname || '"');
    END;
  END LOOP;

  COMMIT;
END;
/

-------------------------------------------------------------------------
-- Case: since all triggers are internalized, do the following:
--       o. create internal triggers for $RT
--       o. drop $TP packages and their bodies (it's generated for each $RT in
--          V7.3. In V8, all triggers are internalized)
--       o. drop $ST triggers because we don't support V8 master and V7.2 
--          (and version earlier than 7.2) snapshot configuration (in V7.2,
--          $ST is generated in master and extracted by snapshot sites)
--       o. drop $RT triggers
-------------------------------------------------------------------------
DECLARE
  sql_cursor   NUMBER;
  rows         NUMBER;
  tab          VARCHAR2(40);           -- master table or snapshot base table

  CURSOR tppkgs IS
    SELECT distinct g.sname, g.oname, g.type, g.reason, g.base_sname,
           g.base_oname, g.base_type, r.master,
           DECODE (o.type, 9,  'PACKAGE', 
                           11, 'PACKAGE BODY',
                           12, 'TRIGGER') ntype
    FROM system.repcat$_generated g, 
         system.repcat$_repobject o,
         system.repcat$_repcat r       -- join with repcat to get master flag
    WHERE ((o.type = 9  AND g.reason = 6)                      -- $TP package
        OR (o.type = 11 AND g.reason = 6)                      -- $TP pkg body
        OR (o.type = 12 AND g.reason = 0)                      -- $ST trigger
        OR (o.type = 12 AND g.reason = 7))                     -- $RT trigger
      AND o.type = g.type           -- join on primary key for efficiency
      AND o.sname = g.sname
      AND o.oname = g.oname
      AND o.gname = r.sname        -- sname in repcat is interpreted as gname
      ORDER BY g.type DESC;           --   drop package body (type 11) first 
                                      -- before droping its spec (type 9)
  sync_up_rep_exception  EXCEPTION;   -- remember exception in sync_up_rep

BEGIN
  sql_cursor := DBMS_SQL.OPEN_CURSOR;

  FOR rec IN tppkgs LOOP
    BEGIN
      IF DBMS_REPCAT_UTL.OBJECT_EXISTS(rec.sname, rec.oname, rec.ntype) THEN
        -- Create internal triggers for replication ($RT)
        IF rec.type = 12 AND rec.reason = 7 THEN
          -- rec.master is always NOT NULL
          BEGIN
            -- create internal triggers for base table
            IF rec.master = 'Y' THEN                       
              -- for master site
              tab := rec.base_oname;
            ELSE                               
              -- for updatable snapshot site
              -- (sowner, vname) has unique index on snap$
              SELECT tname INTO tab
                FROM sys.snap$
                  WHERE sowner = rec.base_sname AND
                         vname = rec.base_oname;
            END IF;
            dbms_reputil.sync_up_rep(rec.base_sname, tab);

          EXCEPTION
            WHEN others THEN
              DBMS_SYSTEM.KSDWRT(2, '  Error ' || TO_CHAR(SQLCODE) || ': ' ||
                                   SQLERRM);
              DBMS_SYSTEM.KSDWRT(2, '  Error in generating internal trigger '
                                      || 'for "' || rec.base_sname || '"."' || 
                                         tab || '"');
              -- reraise: skip the dropping of $RT so that at least one trigger
              -- will exist since we can't generate the internal triggers
              RAISE sync_up_rep_exception; 
          END;
        END IF;
        DBMS_SQL.PARSE(sql_cursor, 'DROP ' || rec.ntype || ' "' || 
          rec.sname || '"."' || rec.oname || '"', DBMS_SQL.NATIVE);
        rows := DBMS_SQL.EXECUTE(sql_cursor);
      END IF;

      --- Delete from RepCat if there is no exception or the obj not exists
      DELETE FROM system.repcat$_generated
        WHERE sname = rec.sname AND oname = rec.oname AND type = rec.type;
      DELETE FROM system.repcat$_repobject
        WHERE sname = rec.sname AND oname = rec.oname AND type = rec.type;
      COMMIT;

    EXCEPTION 
      WHEN sync_up_rep_exception THEN
        NULL;
      WHEN others THEN
        DBMS_SYSTEM.KSDWRT(2, '  Error ' || TO_CHAR(SQLCODE) || ': ' ||
                             SQLERRM);
        DBMS_SYSTEM.KSDWRT(2, '  error in dropping object "' || rec.sname 
                              || '"."' || rec.oname || '"');
      COMMIT;
    END;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(sql_cursor); 
  COMMIT;
END;
/

-------------------------------------------------------------------------
-- recompile $RR, $RV, $RP and replicated procedures in generated code
-- (migrated packages are in invalid state without recompilation)
-------------------------------------------------------------------------
-- recompile all invalid objects (including package, package body, procedures)
DECLARE
  sql_cursor  NUMBER;
  rows        NUMBER;
 
  -- compile $RV and $RR first because $RP calls $RV and $RR
  CURSOR invgenobjs IS
    SELECT distinct rg.sname, dbo.object_name oname, rg.type, dbo.status,
           DECODE (dbo.object_type, 'PACKAGE BODY', 'BODY', '') pkg_body,
           DECODE (dbo.object_type, 'PROCEDURE', 'PROCEDURE', 
                                    'PACKAGE') object_type
    FROM system.repcat$_generated rg,
         sys.dba_objects dbo
    WHERE rg.type IN (7, 9, 11)
      AND dbo.object_type IN ('PROCEDURE', 'PACKAGE', 'PACKAGE BODY')
      AND dbo.status = 'INVALID'
      AND dbo.owner = rg.sname
    ORDER BY dbo.object_name DESC;

BEGIN
  sql_cursor := DBMS_SQL.OPEN_CURSOR;

  FOR rec IN invgenobjs LOOP
    BEGIN
      DBMS_SQL.PARSE(sql_cursor, 'ALTER ' || rec.object_type || ' "' || 
            rec.sname || '"."' || rec.oname || '" COMPILE ' || rec.pkg_body, 
            DBMS_SQL.NATIVE);
      rows := DBMS_SQL.EXECUTE(sql_cursor);

    EXCEPTION
      WHEN others THEN
        NULL;
    END;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(sql_cursor);
END;
/

Rem ===========================================================================
Rem END STAGE 1: post-catrep upgrade from 7.3.4 to 8.0.2
Rem ===========================================================================

Rem ===========================================================================
Rem BEGIN STAGE 2: post-catrep upgrade from 8.0.2 to 8.0.3
Rem ===========================================================================

Rem This has been pulled out of the old cat8003r.sql
Rem Here's the old modification history
Rem
Rem     masubram   05/13/97 -  identify snap reg info by snapmaster = NULL pred
Rem     liwong     04/17/97 -  Fix for AQ change
Rem     liwong     03/13/97 -  Created 


DECLARE
  sql_cursor   NUMBER;
  rows         NUMBER;

  CURSOR pkgs IS
    SELECT sname, oname, DECODE(type, 9, 'PACKAGE', 11, 'PACKAGE BODY') ntype,
           type
    FROM system.repcat$_repobject
    WHERE oname = 'REP$WHAT_AM_I'
       AND type IN (9, 11)
    ORDER BY sname, type DESC;  -- drop package body first, then drop its spec

BEGIN
  sql_cursor := DBMS_SQL.OPEN_CURSOR;

  FOR rec IN pkgs LOOP
    BEGIN
      IF DBMS_REPCAT_UTL.OBJECT_EXISTS(rec.sname, rec.oname, rec.ntype) THEN
        -- Here, we can not use dbms_repcat_utl.drop_an_object because 
        -- it needs to get propagator name and propagator may NOT even exist
        -- at this point
        DBMS_SQL.PARSE(sql_cursor,    
          'DROP ' || rec.ntype || ' "' || rec.sname || '"."' || 
          rec.oname || '"',
          DBMS_SQL.NATIVE);
        rows := DBMS_SQL.EXECUTE(sql_cursor);
      END IF;

      --- Delete from RepCat if there is no exception or the object not exists
      DELETE FROM system.repcat$_repprop
        WHERE sname = rec.sname AND oname = rec.oname AND type = rec.type;

      DELETE FROM system.repcat$_repobject
        WHERE sname = rec.sname AND oname = rec.oname AND type = rec.type;
      COMMIT;

    EXCEPTION 
      WHEN others THEN
          DBMS_SYSTEM.KSDWRT(2, '  Error ' || TO_CHAR(SQLCODE) || ': ' || 
                             SQLERRM);
          DBMS_SYSTEM.KSDWRT(2, '  Error in dropping ' || rec.ntype || ' "' || 
                                rec.sname || '"."' || rec.oname || '"');
    END;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(sql_cursor); 
  COMMIT;
END;
/

-- populate recipient_key
BEGIN
  dbms_repcat_mig_internal.generate_recipient_key;
END;
/

-- compile invalid packages
BEGIN
  dbms_repcat_mig_internal.recompile_pkg_procs;
END;
/

-- move any existing snapshot registration information from repcat$_repcat
-- to the new table repcat$_snapgroup
INSERT INTO system.repcat$_snapgroup(gname, dblink, group_comment, rep_type) 
  SELECT sname, dblink, master_comment, 2 FROM system.repcat$_repschema
  WHERE masterdef = 'N' AND master = 'N' and snapmaster IS NULL
/

-- Delete the old registration information
DELETE FROM system.repcat$_repschema 
  WHERE masterdef = 'N' AND master = 'N' and snapmaster IS NULL
/

COMMIT
/

----------------------------------
-- populate def$_destination and
-- add back constraint repcat$_repschema_dest if necessary
----------------------------------
DECLARE
  sql_cursor   NUMBER;
  rows         NUMBER;
BEGIN
 
  BEGIN
    insert into system.def$_destination  (dblink, last_delivered, disabled)
      select distinct dblink, 0, 'F' from system.repcat$_repschema;
    COMMIT;
  EXCEPTION
    WHEN others THEN
      DBMS_SYSTEM.KSDWRT(1, '  Error ' || TO_CHAR(SQLCODE) || ': ' ||
                            SQLERRM);
      DBMS_SYSTEM.KSDWRT(1, '  Fail to populate system.def$_destination');
  END;
 
  sql_cursor := DBMS_SQL.OPEN_CURSOR;

  BEGIN
    DBMS_SYSTEM.KSDWRT(2, '  Replication after-migration: trying to add ' ||
                          '    constraint repcat$_repschema_dest');
    DBMS_SQL.PARSE(sql_cursor,
      'alter table system.repcat$_repschema ' ||
      '  add CONSTRAINT repcat$_repschema_dest FOREIGN KEY(dblink) ' ||
      ' REFERENCES system.def$_destination(dblink) ',
        DBMS_SQL.NATIVE);
      rows := DBMS_SQL.EXECUTE(sql_cursor);
    DBMS_SYSTEM.KSDWRT(2, '  Replication after-migration: ' ||
      '    constraint repcat$_repschema_dest is added successfully');
  EXCEPTION
    WHEN others THEN
      DBMS_SYSTEM.KSDWRT(1, '  Error ' || TO_CHAR(SQLCODE) || ': ' ||
                            SQLERRM);
      DBMS_SYSTEM.KSDWRT(1, '  Failed to add constraint ' ||
                      'repcat$_repschema_dest for replication migration');
  END;
  DBMS_SQL.CLOSE_CURSOR(sql_cursor);
END;
/

Rem ===========================================================================
Rem END STAGE 2: post-catrep upgrade from 8.0.2 to 8.0.3
Rem ===========================================================================

Rem ===========================================================================
Rem BEGIN STAGE 3: post-catrep upgrade from 8.0.3 to the new release
Rem ===========================================================================

@@r0800030.sql

Rem ===========================================================================
Rem END STAGE 3: post-catrep upgrade from 8.0.3 to the new release
Rem ===========================================================================

Rem *************************************************************************
Rem END r0703040.sql
Rem *************************************************************************
