Oracle7 Server Distributed Systems Volume II: Replicated Data

DBMS_RECTIFIER_DIFF.DIFFERENCES

Purpose

To determine the differences between two tables. For additional information, refer to

Syntax

The parameters for the DIFFERENCES procedure are described in Table 12 - 52, and the exceptions are listed in Table 12 - 53. The syntax for this procedure is shown below.

DBMS_RECTIFIER_DIFF.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 := '', |
            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)

Parameter Description
sname1 The name of the schema at REFERENCE_SITE.
oname1 The name of the table at REFERENCE_SITE.
reference_site The name of the reference database site. The default, NULL, indicates the current site.
sname2 The name of the schema at COMPARISON_SITE.
oname2 The name of the table at COMPARISON_SITE.
comparison_site The name of the comparison database site. The default, NULL, indicates the current site.
where_clause Only rows satisfying this restriction are selected for comparison. The default, NULL or '', indicates that all rows should be compared.
column_list A comma-separated list of one or more column names in the table that you want to have compared. You must not have any white space before or after the comma. The default, NULL or '', indicates that all columns be compared. The column list must include the primary key (or its SET_COLUMNS equivalent).
array_columns A PL/SQL table of column names that you want compared for the two tables. Indexing begins at 1, and the final element of the array must be NULL. If position 1 is NULL, all columns are used.
missing_rows_sname The name of the schema in which the missing rows tables are located.
missing_rows_oname1 The name of the table at MISSING_ROWS_SITE that stores information about the rows in the table at REFERENCE site missing from the table at COMPARISON site and the rows at COMPARISON site missing from the table at REFERENCE site.
missing_rows_oname2 The name of the table at MISSING_ROWS_SITE that stores about the missing rows. This table has three columns: the rowid of the row in the MISSING_ROWS_ONAME1 table, the name of the site at which the row is present, and the name of the site from which the row is absent.
missing_rows_site The name of the site where the MISSING_ROWS_ONAME1 and MISSING_ROWS_ONAME2 tables are located. The default, NULL or '', indicates that the tables are located at the current site.
max_missing The maximum number of rows that should be inserted into the MISSING_ROWS_ONAME1 table. Once this number is reached, the routine returns normally, even if more differences exist.
commit_rows The maximum number of rows to insert into MISSING_ROWS_ONAME1 before a COMMIT occurs. By default, a COMMIT occurs after 500 inserts. An empty string ('') or NULL indicates that a COMMIT should only be issued after all rows have been inserted.

Table 12 - 52. Parameters for DBMS_RECTIFIER_DIFF.DIFFERENCES

Exception Description
23365 nosuchsite Database site could not be found.
23366 badnumber COMMIT_ROWS parameter less than 1.
23367 missingprimarykey Column list must include primary key (or SET_COLUMNS equivalent).
23368 badname NULL or empty string for table or schema name.
23369 cannotbenull Parameter cannot be NULL.
23370 notshapeequivalent Tables being compared are not shape equivalent. Shape refers to the number of columns, their column names, and the column data types.
23371 unknowncolumn Column does not exist.
23372 unsupportedtype Type not supported.
commfailure Remote site is inaccessible.
missingobject Table does not exist.

Table 12 - 53. Exceptions for DBMS_RECTIFIER_DIFF.DIFFERENCES

Restrictions

The error ORA-00001: Unique constraint (XXXX.XXXXX) violated is issued when there are any unique or primary key constraints on the MISSING_ROWS_DATA table.

RECTIFIER_DIFF.DIFFERENCES can only be invoked against release 7.3 sites or higher.

Parameter	Description
`sname1`	The name of the schema at REFERENCE_SITE.
`oname1`	The name of the table at REFERENCE_SITE.
`reference_site`	The name of the reference database site. The default, NULL, indicates the current site.
`sname2`	The name of the schema at COMPARISON_SITE.
`oname2`	The name of the table at COMPARISON_SITE.
`comparison_site`	The name of the comparison database site. The default, NULL, indicates the current site.
`where_clause`	Only rows satisfying this restriction are selected for comparison. The default, NULL or '', indicates that all rows should be compared.
`column_list`	A comma-separated list of one or more column names in the table that you want to have compared. You must not have any white space before or after the comma. The default, NULL or '', indicates that all columns be compared. The column list must include the primary key (or its SET_COLUMNS equivalent).
`array_columns`	A PL/SQL table of column names that you want compared for the two tables. Indexing begins at 1, and the final element of the array must be NULL. If position 1 is NULL, all columns are used.
`missing_rows_sname`	The name of the schema in which the missing rows tables are located.
`missing_rows_oname1`	The name of the table at MISSING_ROWS_SITE that stores information about the rows in the table at REFERENCE site missing from the table at COMPARISON site and the rows at COMPARISON site missing from the table at REFERENCE site.
`missing_rows_oname2`	The name of the table at MISSING_ROWS_SITE that stores about the missing rows. This table has three columns: the rowid of the row in the MISSING_ROWS_ONAME1 table, the name of the site at which the row is present, and the name of the site from which the row is absent.
`missing_rows_site`	The name of the site where the MISSING_ROWS_ONAME1 and MISSING_ROWS_ONAME2 tables are located. The default, NULL or '', indicates that the tables are located at the current site.
`max_missing`	The maximum number of rows that should be inserted into the MISSING_ROWS_ONAME1 table. Once this number is reached, the routine returns normally, even if more differences exist.
`commit_rows`	The maximum number of rows to insert into MISSING_ROWS_ONAME1 before a COMMIT occurs. By default, a COMMIT occurs after 500 inserts. An empty string ('') or NULL indicates that a COMMIT should only be issued after all rows have been inserted.

Exception	Description
23365 nosuchsite	Database site could not be found.
23366 badnumber	COMMIT_ROWS parameter less than 1.
23367 missingprimarykey	Column list must include primary key (or SET_COLUMNS equivalent).
23368 badname	NULL or empty string for table or schema name.
23369 cannotbenull	Parameter cannot be NULL.
23370 notshapeequivalent	Tables being compared are not shape equivalent. Shape refers to the number of columns, their column names, and the column data types.
23371 unknowncolumn	Column does not exist.
23372 unsupportedtype	Type not supported.
commfailure	Remote site is inaccessible.
missingobject	Table does not exist.