COMPARE_OLD_VALUES Procedure

This procedure specifies whether to compare old column values during propagation of deferred transactions at each master site for each nonkey column of a replicated table for updates and deletes. The default is to compare old values for all columns. You can change this behavior at all master sites and materialized view sites by invoking DBMS_REPCAT.COMPARE_OLD_VALUES at the master definition site.

When you use user-defined types, you can specify leaf attributes of a column object, or you can specify an entire column object. For example, if a column object named cust_address has street_address as an attribute, then you can specify cust_address.street_address for the column_list parameter or as part of the column_table parameter, or you can specify only cust_address.

When performing equality comparisons for conflict detection, Oracle treats objects as equal only if one of the following conditions is true:

Both objects are atomically NULL (the entire object is NULL)
All of the corresponding attributes are equal in the objects

Given these conditions, if one object is atomically NULL while the other is not, then Oracle does not consider the objects to be equal. Oracle does not consider MAP and ORDER methods when performing equality comparisons.

Syntax

DBMS_REPCAT.COMPARE_OLD_VALUES(
   sname           IN  VARCHAR2,
   oname           IN  VARCHAR2,
   { column_list   IN  VARCHAR2,
   | column_table  IN  DBMS_UTILITY.VARCHAR2s | DBMS_UTILITY.LNAME_ARRAY,}
   operation       IN  VARCHAR2 := 'UPDATE',
   compare         IN  BOOLEAN := true );

Note:

This procedure is overloaded. The column_list and column_table parameters are mutually exclusive.

Parameters

Table 18-50 COMPARE_OLD_VALUES Procedure Parameters

Parameter	Description
sname	Schema in which the table is located.
oname	Name of the replicated table. The table can be the storage table of a nested table.
column_list	A comma-delimited list of the columns in the table. There must be no spaces between entries.
column_table	Instead of a list, you can use a PL/SQL index-by table of type `DBMS_REPCAT.VARCHAR2` or `DBMS_UTILITY`.`LNAME_ARRAY` to contain the column names. The first column name should be at position 1, the second at position 2, and so on. Use `DBMS_UTILITY`.`LNAME_ARRAY` if any column name is greater than or equal to 30 bytes, which may occur when you specify the attributes of column objects.
operation	Possible values are: `update`, `delete`, or the asterisk wildcard `'`*`'`, which means update and delete.
compare	If compare is `true`, the old values of the specified columns are compared when sent. If compare is `false`, the old values of the specified columns are not compared when sent. Unspecified columns and unspecified operations are not affected. The specified change takes effect at the master definition site as soon as `min_communication` is `true` for the table. The change takes effect at a master site or at a materialized view site the next time replication support is generated at that site with `min_communication` `true`.

Note:

The operation parameter enables you to decide whether or not to compare old values for nonkey columns when rows are deleted or updated. If you do not compare the old value, then Oracle assumes the old value is equal to the current value of the column at the target side when the update or delete is applied.

See Oracle Database Advanced Replication for more information about reduced data propagation using the COMPARE_OLD_VALUES procedure before changing the default behavior of Oracle.

Exceptions

Table 18-51 COMPARE_OLD_VALUES Procedure Exceptions

Exception	Description
nonmasterdef	Invocation site is not the master definition site.
missingobject	Specified object does not exist as a table in the specified schema waiting for row-level replication information.
missingcolumn	At least one column is not in the table.
notquiesced	Master group has not been quiesced.
typefailure	An illegal operation is specified.
keysendcomp	A specified column is a key column in a table.
dbnotcompatible	Feature is incompatible with database version. Typically, this exception arises when you are trying to compare the attributes of column objects. In this case, all databases must be at 9.0.1 or higher compatibility level.