31
DBMS_RECTIFIER_DIFF

The DBMS_RECTIFIER_DIFF package contains APIs used to detect and resolve data inconsistencies between two replicated sites.

Summary of Subprograms

Table 31-1 DBMS_RECTIFIER_DIFF Package Subprograms

Subprogram	Description
DIFFERENCES procedure	Determines the differences between two tables.
RECTIFY procedure	Resolves the differences between two tables.

DIFFERENCES procedure

This procedure determines the differences between two tables.

Syntax

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);

Note:
This procedure is overloaded. The column_list and array_columns parameters are mutually exclusive.

Parameters

Table 31-2 DIFFERENCES Procedure Parameters

Parameter	Description
sname1	Name of the schema at `REFERENCE_SITE`.
oname1	Name of the table at `REFERENCE_SITE`.
reference_site	Name of the reference database site. The default, `NULL`, indicates the current site.
sname2	Name of the schema at `COMPARISON_SITE`.
oname2	Name of the table at `COMPARISON_SITE`.
comparison_site	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`, indicates the current site.
column_list	A comma-separated list of one or more column names being compared for the two tables. You must not have any white space before or after the comma. The default, `NULL`, indicates that all columns be compared.
array_columns	A PL/SQL table of column names being compared for the two tables. Indexing begins at 1, and the final element of the array must be `NULL`. If position 1 is `NULL`, then all columns are used.
missing_rows_sname	Name of the schema containing the tables with the missing rows.
missing_rows_oname1	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	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	Name of the site where the `MISSING_ROWS_ONAME1` and `MISSING_ROWS_ONAME2` tables are located. The default, `NULL`, indicates that the tables are located at the current site.
max_missing	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, then that many rows are inserted into `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`.
commit_rows	Maximum number of rows to insert to or delete from the reference or comparison table before a `COMMIT` occurs. By default, a `COMMIT` occurs after 500 inserts or 500 deletes. An empty string (' ') or `NULL` indicates that a `COMMIT` should only be issued after all rows for a single table have been inserted or deleted.

Exceptions

Table 31-3 DIFFERENCES Procedure Exceptions

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

Restrictions

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

RECTIFY procedure

This procedure resolves the differences between two tables.

Syntax

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

Note:
This procedure is overloaded. The column_list and array_columns parameters are mutually exclusive.

Parameters

Table 31-4 RECTIFY Procedure Parameters

Parameter	Description
sname1	Name of the schema at `REFERENCE_SITE`.
oname1	Name of the table at `REFERENCE_SITE`.
reference_site	Name of the reference database site. The default, `NULL`, indicates the current site.
sname2	Name of the schema at `COMPARISON_SITE`.
oname2	Name of the table at `COMPARISON_SITE`.
comparison_site	Name of the comparison database site. The default, `NULL`, indicates the current site.
column_list	A comma-separated list of one or more column names being compared for the two tables. You must not have any white space before or after the comma. The default, `NULL`, indicates that all columns be compared.
array_columns	A PL/SQL table of column names being compared for the two tables. Indexing begins at 1, and the final element of the array must be `NULL`. If position 1 is `NULL`, then all columns are used.
missing_rows_sname	Name of the schema containing the tables with the missing rows.
missing_rows_oname1	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	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	Name of the site where the `MISSING_ROWS_ONAME1` and `MISSING_ROWS_ONAME2` tables are located. The default, `NULL`, indicates that the tables are located at the current site.
commit_rows	Maximum number of rows to insert to or delete from the reference or comparison table before a `COMMIT` occurs. By default, a `COMMIT` occurs after 500 inserts or 500 deletes. An empty string (' ') or `NULL` indicates that a `COMMIT` should only be issued after all rows for a single table have been inserted or deleted.

Exceptions

Table 31-5 RECTIFY Procedure Exceptions

Exception	Description
nosuchsite	Database site could not be found.
badnumber	`COMMIT_ROWS` parameter less than 1.
badname	`NULL` or empty string for table or schema name.
dbms_repcat. commfailure	Remote site is inaccessible.
dbms_repcat. missingobject	Table does not exist.

31DBMS_RECTIFIER_DIFF

Summary of Subprograms

Table 31-1 DBMS_RECTIFIER_DIFF Package Subprograms

DIFFERENCES procedure

Syntax

Parameters

Table 31-2 DIFFERENCES Procedure Parameters

Exceptions

Table 31-3 DIFFERENCES Procedure Exceptions

Restrictions

RECTIFY procedure

Syntax

Parameters

Table 31-4 RECTIFY Procedure Parameters

Exceptions

Table 31-5 RECTIFY Procedure Exceptions

31
DBMS_RECTIFIER_DIFF