RECTIFY Procedure

This procedure resolves the differences between two tables. It accepts the storage table of a nested table.

Note:

This procedure cannot be used on LOB columns, nor on columns based on user-defined types.

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 16-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-delimited list of one or more column names being compared for the two tables. You must not have any spaces before or after a comma. The default, `NULL`, indicates that all columns will be compared.
array_columns	A PL/SQL index-by 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` that are missing from the table at `comparison_site`, and information about the rows at `comparison_site` that are missing from the table at `reference_site`.
missing_rows_oname2	Name of the table at `missing_rows_site` that stores information 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 be issued only after all rows for a single table have been inserted or deleted.

Exceptions

Table 16-5 RECTIFY Procedure Exceptions

Exception	Description
nosuchsite	Database site could not be found.
badnumber	The `commit_rows` parameter is 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.