Rem
Rem $Header: dbmshsna.sql 08-oct-96.15:29:31 liwong Exp $
Rem
Rem dbmshsna.sql
Rem
Rem  Copyright (c) Oracle Corporation 1996, 1997, 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      dbmshsna.sql - Header (package specification) of dbms_repcat_SNA
Rem
Rem    DESCRIPTION
Rem      These are utility procedures and functions for snapshots.
Rem      This package should be standalone for snapshots (i.e., it should not
Rem      call other dependent functions/procedures).
Rem
Rem    NOTES
Rem      Must be run when connected to SYS or INTERNAL.
Rem      See prvtbsna.sql for its body.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    hasun       05/07/99  - Modify comments re:Flavors                      
Rem    mimoy       01/06/99  - Add comments: failaltersnaprop, alreadymastered
Rem    liwong      12/16/98  - support for multiple snapshot repgroups         
Rem    hasun       12/12/98  - Modify repcat_import_check()                    
Rem    avaradar    12/09/98  - support for multiple snapshot repgroups         
Rem    jstamos     04/20/98  - add flavor routines                             
Rem    jstamos     03/06/98  - flavor support                                  
Rem    wesmith     09/15/97 -  Untrusted security model enhancements
Rem    hasun       06/05/97 -  Fix comments
Rem    sbalaram    06/03/97 -  more cleanup
Rem    sbalaram    05/09/97 -  cleanup comments
Rem    masubram    04/28/97 -  Fix merge problems
Rem    masubram    04/18/97 -  fix switch snapshot master
Rem    liwong      04/18/97 -  remove sname/execute_as_user in 
Rem                         -  switch_snapshot_master
Rem    masubram    04/13/97 -  use defined constants for (un)register_snapshot_
Rem    masubram    04/01/97 -  comment (un)register_snapshot_group
Rem    masubram    03/13/97 -  Modify definitions for snapshot registration
Rem    celsbern    01/23/97 -  Added register_snapshot_repgroup and
Rem                            unregister_snapshot_repgroup, missing after
Rem                            split.
Rem    celsbern    10/05/96 -  Creation (from splitting prvtrepc.sql)
Rem

CREATE OR REPLACE PACKAGE dbms_repcat_sna AS

  --
  -- NOTE: The type varchar2s is equivalent to the type varchar2s
  --       in the package dbms_repcat.  If you make changes to this
  --       version, please make the same changes to the other version.
  --       
  TYPE varchar2s IS TABLE OF VARCHAR(60) INDEX BY BINARY_INTEGER;

  ----------------------------------------------------------------------------
  PROCEDURE register_snapshot_repgroup(gname    IN VARCHAR2, 
                                       snapsite IN VARCHAR2, 
                                       comment  IN VARCHAR2 := NULL,
                                       rep_type IN NUMBER
                                             := dbms_repcat_decl.reg_unknown,
                                       fname    IN VARCHAR2 := NULL,
                                       gowner   IN VARCHAR2 := 'PUBLIC');
  -- This procedure is used at the master site to manually register
  -- a snapshot repgroup.
  -- Arguments:
  --   gname: Name of the repgroup
  --   snapsite: Site of the snapshot repgroup
  --   comment: comment describing the snapshot repgroup
  --   rep_type: Version of the snapshot group (valid constants are
  --     defined in dbms_repcat package header)
  --   fname: This parameter is reserved for internal use.  
  --          Do not specify this parameter unless directed 
  --          by Oracle Worldwide Customer Support.
  --   gowner: Owner of the repgroup
  ----------------------------------------------------------------------------
  PROCEDURE unregister_snapshot_repgroup(gname    IN VARCHAR2, 
                                         snapsite IN VARCHAR2,
                                         gowner   IN VARCHAR2 := 'PUBLIC');
  -- This procedure is used at the master site to manually unregister
  -- a snapshot repgroup.
  -- Arguments:
  --   gname: Name of the repgroup
  --   snapsite: Site of the snapshot repgroup
  --   gowner: owner of the repgroup
  ----------------------------------------------------------------------------
  PROCEDURE alter_snapshot_propagation(gname            IN VARCHAR2,
                                       propagation_mode IN VARCHAR2,
                                       comment          IN VARCHAR2 := '',
                                       gowner           IN VARCHAR2 
                                                           := 'PUBLIC');
  -- Alter the propagation method of all replication snapshots, procedure,
  -- packages, and package bodies for all snapshot repobjects in the
  -- specified snapshot repgroup.
  --
  -- Altering the propagation method involves regenerating replication
  -- support at the snapshot site.
  --
  -- Arguments:
  --   gname: name of the snapshot object group
  --   propagation_mode: SYNCHRONOUS or ASYNCHRONOUS
  --   comment: comment added to the RepProp view
  --   gowner: owner of the snapshot object group
  --
  -- Exceptions:
  --   notcompat: only databases operating in 7.3 (or later) mode can
  --     use this procedure.
  --   missingrepgroup: replicated object group does not exist
  --   typefailure: incorrect propagation mode specified
  --   commfailure: cannot contact master
  --   failaltersnaprop: snapshot repgroup propagation can be altered only 
  --     when there is no other repgroup with the same master sharing the site.
 ----------------------------------------------------------------------------
  PROCEDURE create_snapshot_repgroup(gname            IN VARCHAR2,
                                     master           IN VARCHAR2,
                                     comment          IN VARCHAR2 := '',
                                     propagation_mode IN VARCHAR2 
                                                         := 'ASYNCHRONOUS',
                                     fname            IN VARCHAR2 := NULL,
                                     gowner           IN VARCHAR2
                                                         := 'PUBLIC');
  -- Create a new empty snapshot repgroup at the local site. The group name
  -- must be a master repgroup at the master database.
  --
  -- Arguments:
  --   gname: name of the replicated object group
  --   master: database to use as the master
  --   comment: comment added to the schema_comment field of RepCat view
  --   propagation_mode: method of propagation for all updatable snapshots
  --     in the object group (SYNCHRONOUS or ASYNCHRONOUS)
  --   fname: This parameter is reserved for internal use.  
  --          Do not specify this parameter unless directed 
  --          by Oracle Worldwide Customer Support.
  --   gowner: owner of the replicated object group
  --
  -- Exceptions:
  --   duplicaterepgroup: if the objectgroup already exists as a repgroup
  --     at the invocation site.
  --   nonmaster: if the given database is not a master site.
  --   commfailure: if the given database is not accessible.
  --   norepoption: if advanced replication option not installed
  --   typefailure: if propagation mode specified incorrectly
  --   missingrepgroup: object group missing at master site
  --   invalidqualifier: connection qualifier specified for master is not
  --     valid for the object group
  --   alreadymastered: if at the local site there is another snapshot 
  --     repgroup with the same group name, but different master.
 ----------------------------------------------------------------------------
  PROCEDURE create_snapshot_repobject(sname             IN VARCHAR2,
                                      oname             IN VARCHAR2,
                                      type              IN VARCHAR2,
                                      ddl_text          IN VARCHAR2 := '',
                                      comment           IN VARCHAR2 := '',
                                      gname             IN VARCHAR2 := '',
                                      gen_objs_owner    IN VARCHAR2 := '',
                                      min_communication IN BOOLEAN := TRUE,
                                      generate_80_compatible
                                                        IN BOOLEAN := TRUE,
                                      gowner            IN VARCHAR2
                                                           := 'PUBLIC');
  -- Add the given object name and type to the RepObject view at the local
  -- snapshot repgroup. The allowed types are `package', `package body',
  -- 'procedure', `snapshot', `synonym', 'trigger', 'index' and `view'.
  --
  -- For objects of type `snapshot', create the row-level replication trigger
  -- and client-side half of the stored package if the underlying table
  -- uses row/column-level replication.
  --
  -- The parameter ddl_text defines the snapshot if the snapshot does not 
  -- already exist. The value of oname should match the snapshot
  -- name defined in the ddl_text.  The snaphot's master should match the
  -- master stored in all_repgroup, this includes the connection qualifier
  -- that may be associated with the master group.
  -- 
  -- gen_objs_owner indicates the schema in which the generated procedural 
  -- wrapper should be install. If this parameter is NULL, the value of the 
  -- sname parameter is used.
  --
  -- If min_communication is TRUE and type is 'SNAPSHOT', the update trigger
  -- sends the new value of a column only if the update statement modifies the
  -- column.  The update trigger sends the old value of the column only if it
  -- is a key column or a column in a modified column group.
  --
  -- If generate_80_compatible is true, deferred RPC's with the TOP
  -- flavor are generated using the 8.0 protocol.
  --
  -- gowner is the owner of the replicated object group
  --
  -- Exceptions:
  --   missingschema if specified owner of generated objects does not exist
  --   nonsnapshot if the invocation site is not a snapshot site.
  --   nonmaster if the master is no longer a master site.
  --   missingobject if the given object does not exist in the master's
  --     replicated object group.
  --   duplicateobject if the given object already exists.
  --   typefailure if the type is not an allowable type.
  --   ddlfailure if the DDL does not succeed.
  --   commfailure if the master is not accessible.
  --   badsnapddl if th ddl was executed but snapshot does not exist
  --   onlyonesnap if only one snapshot for master table can be created
  --   badsnapname if snapshot base table differs from master table
  --   misingrepgroup if replicated object group does not exist
  ----------------------------------------------------------------------------
  PROCEDURE create_snapshot_repschema(sname   IN VARCHAR2,
                                      master  IN VARCHAR2,
                                      comment IN VARCHAR2 := '');
  -- OBSOLETE PROCEDURE: use create_snapshot_repgroup()
  -- Create a new empty snapshot repschema. The schema name must be a master
  -- repschema at the master database. In addition, the schema must also exist
  -- locally as a database schema.
  -- 
  -- Exceptions:
  --   duplicateschema: if the schema already exists as a replicated object  
  --                    group at the invocation site.
  --   nonmaster: if the given database is not a master site.
  --   commfailure: if the given database is not accessible.
  ----------------------------------------------------------------------------
  PROCEDURE drop_snapshot_repgroup(gname         IN VARCHAR2,
                                   drop_contents IN BOOLEAN := FALSE,
                                   gowner        IN VARCHAR2 := 'PUBLIC');
  -- Drop the given snapshot repgroup and optionally all of its contents
  -- at this snapshot site.
  --
  -- Arguments:
  --   gname: name of the replicated object group to be dropped
  --   drop_contents: (see comment above)
  --   gowner: owner of the replicated object group
  --
  -- Exceptions:
  --   nonsnapshot: if the invocation site is not a snapshot site.
  --   missingrepgroup: the replicated object group does not exist
  ----------------------------------------------------------------------------
  PROCEDURE drop_snapshot_repobject(sname        IN VARCHAR2,
                                    oname        IN VARCHAR2, 
                                    type         IN VARCHAR2, 
                                    drop_objects IN BOOLEAN := FALSE);
  -- Remove the given object name from the local replication catalog
  -- and optionally drop the object and dependent objects.
  -- 
  -- Exceptions:
  --   nonsnapshot if the invocation site is not a snapshot site.
  --   missingobject if the given object does not exist.
  --   typefailure if the given type parameter is not supported.
  ----------------------------------------------------------------------------
  PROCEDURE drop_snapshot_repschema(sname         IN VARCHAR2,
                                    drop_contents IN BOOLEAN := FALSE);
  -- OBSOLETE PROCEDURE: use drop_snapshot_repgroup()
  -- Drop the given snapshot repschema and optionally all of its contents
  -- at this snapshot site. In addition, the schema must also exist locally 
  -- as a database schema.
  -- 
  -- Exceptions:
  --   nonsnapshot if the invocation site is not a snapshot site.
  ----------------------------------------------------------------------------
  PROCEDURE generate_snapshot_support(sname             IN VARCHAR2,
                                      oname             IN VARCHAR2,
                                      type              IN VARCHAR2,
                                      gen_objs_owner    IN VARCHAR2 := '',
                                      min_communication IN BOOLEAN := TRUE,
                                      generate_80_compatible
                                                        IN BOOLEAN := TRUE);
  -- If the object exists in the replicated snapshot object group
  -- as an updatable snapshot using row/column-level replication, 
  -- create the row-level replication trigger and stored package.   
  -- 
  -- If the object exists in the replicated object group as a procedure
  -- or package (body), then generate the appropriate wrappers.
  -- 
  -- Parameter gen_objs_owner specifies the schema in which the generated
  -- replication package and wrapper should be installed. If this value is
  -- NULL, then the generated package or wrapper will be installed in the
  -- schema specified by the sname parameter.
  --
  -- If min_communication is TRUE, then the update trigger sends the new value
  -- of a column only if the update statement modifies the column.  The update
  -- trigger sends the old value of the column only if it is a key column or
  -- a column in a modified column group.
  --
  -- If generate_80_compatible is true, deferred RPC's with the TOP
  -- flavor are generated using the 8.0 protocol.
  --
  -- Exceptions:
  --   nonsnapshot: if the invocation site is not a snapshot site.
  --   missingobject: if the given object does not exist as a snapshot in the
  --                  replicated objevt group awaiting row/column-level 
  --                  replication information or as a procedure or package 
  --                  (body) awaiting wrapper generation.
  --   typefailure: if the given type parameter is not supported.
  --   missingschema: if specified owner of generated objects does not exist
  --   missingremoteobject: if the master object has not yet generated
  --                        replication support.
  --   commfailure: if the master is not accessible
  ----------------------------------------------------------------------------
  PROCEDURE refresh_snapshot_repgroup(gname                 IN VARCHAR2,
                                      drop_missing_contents IN BOOLEAN 
                                        := FALSE,
                                      refresh_snapshots     IN BOOLEAN 
                                        := FALSE,
                                      refresh_other_objects IN BOOLEAN 
                                        := FALSE,
                                      gowner                IN VARCHAR2
                                        := 'PUBLIC');
  -- Refresh the RepCat views for the given repgroup and optionally drop
  -- objects no longer in the repgroup.  Consistently refresh the snapshots
  -- if refresh_snapshots is TRUE.  Refresh the other objects if
  -- refresh_other_objects is TRUE. The value in gname must be an existing
  -- snapshot object group in the local database.
  --
  -- Exceptions:
  --   nonsnapshot: if the invocation site is not a snapshot site.
  --   nonmaster: if the master is no longer a master site.
  --   commfailure: if the master is not accessible.
  --   missingrepgroup: if the replicated object group does not exist
  ----------------------------------------------------------------------------
  PROCEDURE refresh_snapshot_repschema(sname                 IN VARCHAR2,
                                       drop_missing_contents IN BOOLEAN
                                         := FALSE,
                                       refresh_snapshots     IN BOOLEAN
                                         := FALSE,
                                       refresh_other_objects IN BOOLEAN
                                         := FALSE,
                                       execute_as_user       IN BOOLEAN);
  PROCEDURE refresh_snapshot_repschema(sname                 IN VARCHAR2,
                                       drop_missing_contents IN BOOLEAN
                                         := FALSE,
                                       refresh_snapshots     IN BOOLEAN
                                         := FALSE,
                                       refresh_other_objects IN BOOLEAN
                                         := FALSE);
  -- OBSOLETE PROCEDURE: use refresh_snapshot_repgroup()
  -- Refresh the RepCat views for the given repgroup and optionally drop
  -- objects no longer in the repschema.  Consistently refresh the snapshots
  -- iff refresh_snapshots is TRUE.  Refresh the other objects if
  -- refresh_other_objects is TRUE. Deferred RPCs to the master site are
  -- pushed as the current session's user if execute_as_user is TRUE
  -- The schema sname must exist as a schema in the local database
  --
  -- Exceptions:
  --   nonsnapshot if the invocation site is not a snapshot site.
  --   nonmaster if the master is no longer a master site.
  --   commfailure if the master is not accessible.
  ----------------------------------------------------------------------------
  PROCEDURE repcat_import_check(gname  IN VARCHAR2 := '',
                                master IN BOOLEAN,
                                sname  IN VARCHAR2 := '',
                                gowner IN VARCHAR2 := 'PUBLIC');
  -- Update the object identifiers and status values in repcat$_repobject
  -- for the given repgroup, preserving object status values other than VALID.
  --
  -- Exceptions:
  --   missingschema: if the replicated object group does not exist.
  --   nonmaster: if master is TRUE and either the database is not a master or
  --              the database is not the expected database.
  --   nonsnapshot: if master is FALSE and the database is not a snapshot site.
  --   missingobject: if a valid repobject in the schema does not exist.
  ----------------------------------------------------------------------------
  PROCEDURE repcat_import_check;
  -- Invoke repcat_import_check(gowner, gname) for each replicated object group
  --
  -- Exceptions:
  --   nonmaster if the database is not the expected database for any
  --     replicated object group.
  --   missingobject if a valid replicated object in any schema does not exist.
  ----------------------------------------------------------------------------
  PROCEDURE switch_snapshot_master(gname      IN VARCHAR2,
                                   master     IN VARCHAR2,
                                   force_fail IN NUMBER := 
                                              dbms_repcat_decl.sw_fail_none,
                                   gowner     IN VARCHAR2 := 'PUBLIC');
  -- Change the master database of the snapshot repgroup to the given
  -- database. The new database must contain a replica of the master
  -- repgroup. Each snapshot in the local repgroup will be completely
  -- refreshed from the new master the next time it is refreshed.
  -- This procedure will raise an error if any snapshot definition query 
  -- is bigger than 32K.
  --
  -- Any snapshot logs should be created at all masters to avoid future
  -- complete refreshes.
  --
  -- Arguments:
  --   gname: name of the snapshot object group
  --   master: name of the new master
  --   gowner: owner of the snapshot object group
  --
  -- Exceptions:
  --   nonsnapshot: if the invocation site is not a snapshot site.
  --   nonmaster: if the given database is not a master site.
  --   commfailure: if the given database is not accessible.
  --   missingrepgroup: snapshot repgroup does not exist
  --   qrytoolong: snapshot definition query is > 32K
  --   alreadymastered: if at the local site there is another snapshot repgroup
  --     with the same group name and mastered at the old master.
  ----------------------------------------------------------------------------
  ---
  --- 
  ---
  --- #######################################################################
  --- #######################################################################
  ---                        INTERNAL PROCEDURES
  ---
  --- The following procedures provide internal functionality and should
  --- not be called directly. Invoking these procedures may corrupt your
  --- replication environment.
  ---
  --- #######################################################################
  --- #######################################################################
  PROCEDURE validate_for_local_flavor(
    gname IN VARCHAR2,
    fname IN VARCHAR2,
    gowner IN VARCHAR2 := 'PUBLIC' );

  PROCEDURE set_local_flavor(
    gname IN VARCHAR2,
    fname IN VARCHAR2,
    validate IN BOOLEAN := TRUE,
    gowner IN VARCHAR2 := 'PUBLIC' );
END dbms_repcat_sna;
/

