SEND_OLD_VALUES Procedure

You have the option of sending old column values during propagation of deferred transactions for each nonkey column of a replicated table when rows are updated or deleted in the table. When min_communication is set to true, the default is the following:

For a deleted row, to send old values for all columns
For an updated row, to send old values for key columns and the modified columns in a column group

You can change this behavior at all master sites and materialized view sites by invoking DBMS_REPCAT.SEND_OLD_VALUES at the master definition site. Then, generate replication support at all master sites and at each materialized view site.

When you use user-defined types, you can specify the leaf attributes of a column object, or 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.

Syntax

DBMS_REPCAT.SEND_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',
   send            IN  BOOLEAN  := true );

Note:

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

Parameters

Table 18-126 SEND_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.
send	If `true`, then the old values of the specified columns are sent. If `false`, then the old values of the specified columns are not 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 specify whether or not to transmit old values for nonkey columns when rows are deleted or updated. If you do not send the old value, then Oracle sends a NULL in place of the old value and 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 information about reduced data propagation using the SEND_OLD_VALUES procedure before changing the default behavior of Oracle.

Exceptions

Table 18-127 SEND_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 send the attributes of column objects. In this case, all databases must be at 9.0.1 or higher compatibility level.