rem 
rem $Header: dbmsrctf.sql 15-jan-98.18:23:09 nlewis Exp $ 
rem 
Rem    NAME
Rem
Rem      dbmsrctf.sql - replication table comparison package spec.
Rem
Rem    NOTES
Rem
Rem      The procedural option is needed to use this facility.
Rem
Rem      This package is installed by sys (connect internal).
Rem
Rem      The repcat tables are defined in catrep.sql and owned by system.
Rem
Rem    DEPENDENCIES
Rem
Rem      The object generator (dbmsobjg) and the replication procedure/trigger
Rem      generator (dbmsgen) must be previously loaded.
Rem
Rem      Uses dynamic SQL (dbmssql.sql) heavily.
Rem
Rem    SECURITY
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem     nlewis     01/15/98 -  remove mlslabel comment
Rem     liwong     11/18/96 -  Added removal of synonym dbms_rectifier_diff
Rem     asurpur    04/09/96 -  Dictionary Protection Implementation
Rem     boki       05/01/95 -  change specification
Rem     boki       03/30/95 -  add more specification to differences()
Rem     boki       02/10/95 -  add new exception for factoring
Rem     boki       02/06/95 -  Creation
Rem

Rem Drop synonym created in dbms_rectifier_diff in catreps.sql
DROP SYNONYM dbms_rectifier_diff
/

---------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE dbms_rectifier_diff AS

  -------------
  -- TYPE DEFINITIONS
  --

  -------------
  -- EXCEPTIONS
  --

  nosuchsite EXCEPTION;
    PRAGMA exception_init(nosuchsite, -23365);
    nosuchsite_num NUMBER := -23365;

  badnumber EXCEPTION;
    PRAGMA exception_Init(badnumber, -23366);
    badnumber_num NUMBER := -23366;

  missingprimarykey EXCEPTION;
    PRAGMA exception_Init(missingprimarykey, -23367);
    missingprimarykey_num NUMBER := -23367;

  badname EXCEPTION;
    PRAGMA exception_Init(badname, -23368);
    badname_num NUMBER := -23368;

  cannotbenull EXCEPTION;
    PRAGMA exception_Init(cannotbenull, -23369);
    cannotbenull_num NUMBER := -23369;

  notshapeequivalent EXCEPTION;
    PRAGMA exception_Init(notshapeequivalent, -23370);
    notshapeequivalent_num NUMBER := -23370;

  unknowncolumn EXCEPTION;
    PRAGMA exception_Init(unknowncolumn, -23371);
    unknowncolumn_num NUMBER := -23371;

  unsupportedtype EXCEPTION;
    PRAGMA exception_Init(unsupportedtype, -23372);
    unsupportedtype_num NUMBER := -23372;

  badmrname EXCEPTION;
    PRAGMA exception_Init(badmrname, -23377);
    badmrname_num NUMBER := -23377;

  -------------
  -- PROCEDURES
  --

  -- Requires: The caller must have already created the tables named
  --   "missing_rows_oname1" and "missing_rows_oname2" in schema
  --   "missing_rows_sname" at the site "missing_rows_site" with the
  --   correct shapes. There must be no white space before or after
  --   commas in "column_list."
  -- Effects:  This routine compares the fragment defined by "column_list"
  --   and the "where_clause" of the table named "sname1.oname1" (truth) at site
  --   "reference_site" with the fragment defined by "column_list" and
  --   "where_clause" of the table  named "sname2.oname2" at site "comparison_site."
  --   It determines what is different and records this information in the
  --   pair of tables named "missing_rows_oname1" and "missing_rows_oname2"
  --   in schema "missing_rows_sname." If the fragments are exactly equivalent,
  --   then the tables "missing_rows_oname1" and "missing_rows_oname2" are
  --   unchanged.  If the fragments are not exactly equivalent, then those
  --   rows in "sname1.oname1" at the "reference_site" that are missing
  --   from "sname2.oname2" at the "comparison_site," and the rows in the
  --   table at "comparision_site" that are missing from the table at 
  --   "reference_site" are inserted into the tables "missing_rows_oname1" and 
  --   "missing_rows_oname2." The routine commits those rows that have been
  --   inserted into the pair of missing rows tables whenever "commit_row"
  --   number of rows have been inserted. Raises exception badnumber if
  --   "commit_rows" is less than 1. If "sname," "oname," "missing_rows_sname,"
  --   or "missing_rows_oname" is NULL or `' (empty string), raise exception 
  --   badname.  Raise badmrname if arguments "missing_rows_oname1" and "oname1"
  --   have the same name; the "oname1" truth table cannot be used as one
  --   of the missing rows tables. Raises exception unknowncolumn if any
  --   column specified in  "column_list" is missing from table 
  --   "sname1.oname1." or from table "sname2.oname2." 
  --   Raises exception missingobject if tables named "oname1," 
  --   "oname2," "missing_rows_oname1," or "missing_rows_oname2" do not exist.
  --   "max_missing" is an integer that refers to the maximum number of rows
  --   that should be inserted into the "missing_rows_oname" table.  If more 
  --   than "max_missing" number of rows is missing, that many rows will be
  --   inserted into the "missing_rows_oname," and  the routine then returns
  --   normally without determining whether more rows are missing; this
  --   argument is useful in the cases that the fragments are so different that
  --   the missing rows table will have too many entries and there's no
  --   point in continuing. Raises exception badnumber if "max_missing" is
  --   less than 1, or NULL.  Raises exception cannotbenull if "max_missing"
  --   is NULL.  Raises exception unsupportedtype if any column data type of
  --   any column in in "column_list" is a long or long raw.
  --       The "where_clause" is used as a predicate to restrict the query
  --   space; if `' (the empty string), then every row is selected, else,
  --   every row that satisfies the predicate is selected. Raises exception
  --   notshapeequivalent if "sname1.oname1" at site "reference_site" is not 
  --   shape equivalent to "sname2.oname2" at site "comparison_site" with
  --   respect to the specified columns in "column_list." 
  --      Duplicate rows are treated as one row under set semantics, and
  --   thus, if they are missing, will appear exactly once in "missing_rows_oname1"
  --   and "missing_rows_oname2" tables. (However, when the table at the
  --   comparison site is rectified, duplicate rows are handled correctly.)
  --      Routine raises exception nosuchsite if the "reference_site,"
  --   "comparison_site," or "missing_rows_site" does not name a site.
  --   Raises exception dbsm_repcat.commfailure if the remote site is inaccessible.
  --   Raises exception missingprimarykey if "column_list" does not contain
  --   the table's primary keys (if multiple columns constitute the primary
  --   key, then all columns must be specified in "column_list").
  --      Two successive invocations of this routine on the same pair of tables
  --   is equivalent to one invocation (without cleaning out the missing rows
  --   tables manually); the missing rows tables remain unchanged.
  --      If the replication option is not installed, then raise exception
  --   dbms_repcat.norepoption.
  --      The table "missing_rows_oname1" must have only the column names given in 
  --   "column_list" and the associated data types, which defines a fragment. The
  --   table "missing_rows_oname2" must have three columns with the associated
  --   datatypes: "present" (type varchar2(128))for the name of the site where
  --   the row appeared, "absent" (type varchar2(128)) for the name of the site
  --   where the row does not appear, and "r_id" (type rowid) to hold the rowid
  --   of the corresponding row in "missing_rows_oname1."
  -- 
  --  Arguments:
  --    *sname1: schema name at reference_site; need not be the fully qualified 
  --             canonical name
  --    *oname1: table name at reference_site
  --    *reference_site:   site name; need not be the fully qualified canonical name; 
  --          legal values are `' (empty string) or NULL, which refer to the
  --          site where this routine is invoked, or any other non-empty string.
  --          Defaults to `'.
  --    *sname2: schema name at comparison_site ; need not be the fully qualified 
  --          canonical name
  --    *oname2: table name at comparision_site
  --    *comparison_site: site name; need not be the fully qualified canonical
  --          name;legal values are `' (empty string) or NULL, which refer to
  --          the site where this routine is invoked, or any other non-empty
  --          string. Defaults to `'. 
  --    *where_clause:  meaningful value is either `' (empty string), NULL, or 
  --          non-empty string; empty string or NULL means no where clause
  --          is provided, so every  row is selected; otherwise, for the
  --          non-empty string, every row that satisfies the predicate is
  --          selected. Defaults to `'.
  --    *column_list: comma-separated list of one or more column names that
  --          define a fragment (partition) of table "sname.oname."  Legal
  --          values are `' (empty string), NULL, or comma-separated list.
  --          If `' or NULL, then all column names are used. Defaults to `'.
  --          Note that list must not have any white space before or 
  --          after the separating comma.
  --    *missing_rows_sname: schema name of schema where missing rows table is.
  --    *missing_rows_oname1: name of a table at "missing_rows_site"  that contains 
  --          information about the rows in "sname1.oname2" that are missing from 
  --          "sname2.oname2" at "comparison_site" and the rows at the
  --          "comparison_site" that are missing from the "reference_site." 
  --    *missing_rows_oname2: the name of the table at site "missing_rows_site"
  --          that holds information about whether a missing row is present at
  --          a site and absent from another site and links this table with
  --          "missing_rows_oname1" to identify the actual missing row.
  --    *missing_rows_site:  The site where the "missing_rows_oname1" and 
  --          "missing_rows_oname2" tables are located; legal values
  --          are `' (empty string) or NULL, which refers to the site where
  --          this routine is invoked, or legal site name. Defaults to `'.
  --    *max_missing: a number that refers to the maximum number of rows inserted 
  --         into the "missing_rows_tab" table; the routine returns normally
  --         when that limit is exceeded.  Legal  integer values must be
  --         greater than or equal to 1. Value cannot be NULL.
  --    *commit_rows:  commit the rows inserted into "missing_rows_tab" every 
  --         "commit_row" number of rows.  Legal values are `' (empty string),
  --         NULL, or an integer value that must start at 1. `' or NULL means
  --         that all rows are inserted before committing. Defaults to 500.
  --
  --  Exceptions:
  --      nosuchsite 
  --      badnumber
  --      missingprimarykey 
  --      badname
  --      cannotbenull
  --      notshapeequivalent
  --      unknowncolumn
  --      unsupportedtype
  --      badmrname
  --      dbms_repcat.commfailure 
  --      dbms_repcat.missingobject
  --      dbms_repcat.norepoption

  PROCEDURE differences	         (sname1	       IN VARCHAR2,
				  oname1	       IN VARCHAR2,
				  reference_site       IN VARCHAR2 := '',
				  sname2	       IN VARCHAR2,
				  oname2	       IN VARCHAR2,
				  comparison_site      IN VARCHAR2 := '',
				  where_clause	       IN VARCHAR2 := '',
				  column_list	       IN VARCHAR2 := '',
				  missing_rows_sname   IN VARCHAR2,
				  missing_rows_oname1  IN VARCHAR2,
				  missing_rows_oname2  IN VARCHAR2,
				  missing_rows_site    IN VARCHAR2 := '',
				  max_missing          IN INTEGER,
				  commit_rows	       IN INTEGER := 500);

  PROCEDURE differences          (sname1	       IN VARCHAR2,
  				  oname1	       IN VARCHAR2,
				  reference_site       IN VARCHAR2 := '',
				  sname2	       IN VARCHAR2,
  				  oname2	       IN VARCHAR2,
				  comparison_site      IN VARCHAR2 := '',
				  where_clause	       IN VARCHAR2 := '',
				  array_columns	       IN dbms_utility.name_array,
				  missing_rows_sname   IN VARCHAR2,
				  missing_rows_oname1  IN VARCHAR2,
				  missing_rows_oname2  IN VARCHAR2,
				  missing_rows_site    IN VARCHAR2 := '',
				  max_missing          IN INTEGER,
				  commit_rows	       IN INTEGER := 500);

  -- Requires:  The caller must have already created the tables named 
  --    "missing_rows_oname1" and "missing_rows_oname2" in schema
  --    "missing_rows_sname" at the site "missing_rows_site" with the
  --    correct shapes. "column_list" must specify the same number and
  --    same names of columns that are in "missing_rows_oname1;" both have 
  --    exactly the same column names. In short, all the same arguments
  --    used in a call to differences(..., column_list, ...) must be
  --    used in this routine to rectify the table at the comparison_site.
  --    The tables "sname1.oname1@reference_site" and "sname2.oname2@comparison_site"
  --    must be the same ones used in a previous invocation of differences().
  --    There must be no white space before or after commas in "column_list."
  -- Effects: This routine uses the information in table "missing_rows_oname1" and 
  --   "missing_rows_oname2" in schema "missing_rows_sname" to rectify table named 
  --   "sname2.oname2" at site "comparison_site."  Rows are deleted from 
  --   "sname2.oname2" at "comparison_site" that are not in "sname1.oname1" at 
  --   "reference_site."  Rows missing from "sname2.oname2" at "comparison_site" are 
  --   inserted. When this routine returns normally, the two tables are exactly
  --   equivalent.  In short, extraneous rows are deleted from the table
  --   "sname2.oname2" at "comparison_site" and missing rows are inserted
  --   into the table. If tables "missing_rows_oname1" and "missing_rows_oname2"
  --   are empty, then this routine has no effect.   Commit every "commit_rows"
  --   number of rows are deleted from "sname2.oname2"; commit every "commit_rows"
  --   number of rows are inserted into "sname2.oname2."
  --      Raises exception nosuchsite if the "reference_site," 
  --   "comparison_site,"  or "missing_rows_site" does not name a site.
  --   Raises exception badnumber if "commit_rows" is
  --   less than 1. Raises exception commfailure if the remote site 
  --   inaccessible. If "sname1," "oname2," "sname2," "oname2," "missing_rows_oname1," 
  --   or "missing_rows_oname2" is NULL or `' (empty string), raise exception badname. 
  --   Raise badmrname if "missing_rows_oname1" and "oname1"
  --   have the same name; the "oname1" truth table cannot be used as one
  --   of the missing rows tables.
  --   Raises exception missingobject if tables named "oname1," "oname2," 
  --   "missing_rows_oname1" or "missing_rows_oname2" do not exist.
  --      If table "sname1.oname1" (truth) at the reference site contains duplicate
  --   rows, those rows are treated under set semantics as one single row
  --   and will appear in the "missing_rows_oname1" and "missing_rows_oname2"
  --   exactly once.  When the table at the comparison site is rectified,
  --   the duplicate rows are in fact inserted into the table at the comparison_site.
  --   If there are duplicate rows at the table at the 
  --   comparison site, then all rows will be deleted. If replication is
  --   turned on, this routine will remember that state, turn replication
  --   off to allow rectifying to proceed, and then reinstate the original state.
  --      The tables  "missing_rows_oname1" and "missing_rows_oname2" cannot contain
  --   differences for different pairs; the routine must only be used to
  --   rectify a pair of table whose differences are in the associated
  --   missing rows tables.  The table "missing_rows_oname1"
  --   must have only the column names given in "column_list" and the
  --   associated data types, which defines a fragment. The table 
  --   "missing_rows_oname2" must have three columns with the associated datatypes: 
  --   "present" (type varchar2(128))for the name of the site where the row appeared, 
  --   "absent" (type varchar2(128)) for the name of the site where the row
  --   does not appear, and "r_id" (type rowid) to hold the rowid of the
  --   corresponding row in "missing_rows_oname1."
  --      If the replication option is not installed, then raise exception
  --   dbms_repcat.norepoption.
  -- 
  -- Arguments: 
  --    *sname1: name of schema at "reference_site;" need not be the
  --             fully qualified canonical name.
  --    *oname1: name of table at "reference_site"
  --    *reference_site:   site name; need not be the fully qualified
  --             canonical name;  legal values are `' (empty string) or
  --             NULL, which refer to the site where this routine is invoked,
  --             or any other non-empty string. Defaults to `'.
  --    *sname2: schema name at "comparison_site;" need not be the fully qualified 
  --             canonical name
  --    *oname2: name of table at "comparison_site"
  --    *comparison_site:  site name; need not be the fully qualified canonical
  --             name.  Legal values are `' (empty string) or NULL, which refer
  --             to the site where this routine is invoked, or any other
  --             non-empty string. Defaults to `'.
  --    *column_list: comma-separated list of one or more column names that
  --             define a fragment (partition) of table "sname.oname."  Legal
  --             values are `' (empty string), NULL, or comma-separated list.
  --             If `' or NULL, then all column names are used. Defaults to `'.
  --             Note that list must not have any white space before or 
  --             after the separating comma.
  --    *missing_rows_sname: schema name of schema where missing rows table
  --             is located.
  --    *missing_rows_oname1: name of a table at "missing_rows_site" that
  --             contains information about the rows in "sname1.oname2" that
  --             are missing from "sname2.oname2" at "comparison_site" and
  --             the rows at the "comparison_site" that are missing from
  --             the "reference_site." 
  --    *missing_rows_oname2: the name of the table at site "missing_rows_site"
  --             that holds information about whether a missing row is present
  --             at a site and absent from another site and links this table
  --             with "missing_rows_oname1" to identify the actual missing row.
  --    *missing_rows_site:  The site where the "missing_rows_oname1" and
  --             "missing_rows_oname2" tables are
  --             located; legal values are `' (empty string) or NULL, which
  --             refers to the site where this routine is invoked, or legal
  --             site name. Defaults to `'.
  --    *commit_rows:  commit the rows deleted from "sname2.oname2" every
  --             "commit_rows" number of rows. Similarly, commit the rows
  --             inserted intot "sname.oname2" "commit_rows" number of rows.
  --             Legal values are `' (empty string), NULL, or an integer value that must
  --             start at 1. `' or NULL means that all rows are deleted or inserted
  --             before committing. Defaults to 500.
  --
  -- Exceptions:
  --    nosuchsite 
  --    dbms_repcat.commfailure
  --    badnumber
  --    badname
  --    dbms_repcat.missingobject
  --    dbms_repcat.norepoption

  PROCEDURE rectify          (	sname1		       IN VARCHAR2,
				oname1		       IN VARCHAR2,
				reference_site         IN VARCHAR2 := '',
				sname2		       IN VARCHAR2,
				oname2		       IN VARCHAR2,
				comparison_site        IN VARCHAR2 := '',
			        column_list            IN VARCHAR2 := '',
				missing_rows_sname     IN VARCHAR2,
				missing_rows_oname1    IN VARCHAR2,
				missing_rows_oname2    IN VARCHAR2,
				missing_rows_site      IN VARCHAR2 := '',
				commit_rows	       IN INTEGER := 500);

  PROCEDURE rectify          (	sname1		       IN VARCHAR2,
				oname1		       IN VARCHAR2,
				reference_site         IN VARCHAR2 := '',
				sname2		       IN VARCHAR2,
				oname2		       IN VARCHAR2,
				comparison_site        IN VARCHAR2 := '',
			        array_columns          IN dbms_utility.name_array,
				missing_rows_sname     IN VARCHAR2,
				missing_rows_oname1    IN VARCHAR2,
				missing_rows_oname2    IN VARCHAR2,
				missing_rows_site      IN VARCHAR2 := '',
				commit_rows	       IN INTEGER := 500);


  PROCEDURE turn_replication_off;

  PROCEDURE turn_replication_on;

end;
/
grant execute on dbms_rectifier_diff to execute_catalog_role
/


