#

| Chapter 16 Conflict Resolution |

## 16.5 Assigning Resolution Methods with DBMS_REPCAT

Once you have configured your column, priority, and/or site priority groups, you can assign conflict resolution techniques to your replicated tables.

### 16.5.1 About Resolution Methods

In addition to column groups, priority groups, and site priority groups, the advanced replication option includes eleven other built-in resolution methods to handle update and uniqueness conflicts (see Table 16.14). You can also write your own resolution handlers. In particular, if you require a delete conflict handler, you must write your own because Oracle does not supply one.

Table 16.14: Built-in Conflict Resolution Methods
Conflict Type | Method Name | Comments
--- | --- | ---
Update | MINIMUM | Data from the row having the minimum value for the designated column prevails. Data is guaranteed to converge if the value is always decreasing, or if there are fewer than three master sites.
| MAXIMUM | Data from the row having the maximum value for the designated column prevails. Data is guaranteed to converge if the value is always increasing, or if there are fewer than three master sites.
| EARLIEST TIME-STAMP | Data from the row having the earliest time-stamp for the designated column prevails. Data is guaranteed to converge if there are fewer than three master sites.
| LATEST TIME-STAMP | Data from the row having the latest timestamp for the designated column prevails. Data is guaranteed to converge if the value is always increasing, or if there are fewer than three master sites.
| OVERWRITE | Intended for a single master site with one or more updateable snapshot sites. Data from the site originating the update prevails. Convergence is not guaranteed with more than one master site.
| DISCARD | Intended for a single master site with one or more updateable snapshot sites. Data from the site originating the update is discarded. Convergence is not guaranteed with more than one master site.
| ADDITIVE | Intended for use with a column group con-sisting of a single numeric column. Oracle determines the new value of the column by adding the difference between the old value and new value at the originating site to the current value at the destination site. Convergence is guaranteed for any number of master sites.
| AVERAGE | Intended for a single master site with one or more updateable snapshot sites. Oracle determines the new value of the column by averaging the current value at the destination with the value from the originating site. Convergence is not guaranteed with more than one master site.
Uniqueness | APPEND SITE NAME | Oracle resolves unique key violations (DUP_VAL_ON_INDEX) by appending the global name of the destination site (up to the first `.') to the offending column. The column must be a CHAR or VARCHAR2 type. Convergence is not guaranteed with more than one master site.
APPEND SEQUENCE | Oracle resolves unique key violations (DUP_VAL_ON_INDEX) by appending a generated sequence number to the offending column. The column must be a CHAR or VARCHAR2 type. Convergence is not guaranteed with more than one master site.
DISCARD | Oracle resolves unique key violations (DUP_VAL_ON_INDEX) by ignoring (i.e., not inserting) the new row. Convergence is not guaranteed with more than one master site.

You'll use the following procedures to manipulate the conflict resolution methods associated with a given table:

ADD_<conflicttype>_RESOLUTION
DROP_<conflicttype>_RESOLUTION
COMMENT_ON_<conflicttype>_RESOLUTION

<conflicttype> can be UPDATE, UNIQUE, or DELETE. Therefore, the complete set of procedures in this category follows:

ADD_UPDATE_RESOLUTION
ADD_UNIQUE_RESOLUTION
ADD_DELETE_RESOLUTION
DROP_UPDATE_RESOLUTION
DROP_UNIQUE_RESOLUTION
DROP_DELETE_RESOLUTION
COMMENT_ON_UPDATE_RESOLUTION
COMMENT_ON_UNIQUE_RESOLUTION
COMMENT_ON_DELETE_RESOLUTION

#### 16.5.1.1 The DBMS_REPCAT.ADD_<conflicttype>_RESOLUTION

The ADD_<conflicttype>_RESOLUTION procedure adds a conflict resolution type to a table. The value of <conflicttype> can be UPDATE, UNIQUE, or DELETE. Here are the specifications:

>     PROCEDURE DBMS_REPCAT.ADD_UPDATE_RESOLUTION
>        (sname IN VARCHAR2,
>         oname IN VARCHAR2,
>         column_group IN VARCHAR2,
>         sequence_no IN NUMBER,
>         method IN VARCHAR2,
>         {parameter_column_name  IN dbms_repcat.varchar2s,|
>         parameter_column_name IN VARCHAR2,}
>         priority_group IN VARCHAR2 := NULL,
>         function_name IN VARCHAR2 := NULL,
>         comment IN VARCHAR2 := NULL);

>     PROCEDURE DBMS_REPCAT.ADD_UNIQUE_RESOLUTION
>        (sname IN VARCHAR2,
>         oname IN VARCHAR2,
>         constraint_name IN VARCHAR2,
>         sequence_no IN NUMBER,
>         method IN VARCHAR2,
>         {parameter_column_name  IN dbms_repcat.varchar2s, |
>         parameter_column_name IN VARCHAR2,}
>         comment IN VARCHAR2 := NULL);

>     PROCEDURE DBMS_REPCAT.ADD_DELETE_RESOLUTION
>        (sname IN VARCHAR2,
>         oname IN VARCHAR2,
>         sequence_no IN NUMBER,
>         {parameter_column_name  IN dbms_repcat.varchar2s, |
>         parameter_column_name IN VARCHAR2,}
>         function_name IN VARCHAR2 := NULL,
>         comment IN VARCHAR2 := NULL);

Parameters are summarized in the following table.

Name | Description
--- | ---
sname | Name of the schema containing the replicated schema. Defaults to current user.
oname | Name of the replicated table.
column_group | ADD_UPDATE_RESOLUTION only. Column group for which the conflict resolution method is being defined.
constraint_name | ADD_UNIQUE_RESOLUTION only. Name of the constraint name or unique index for which the conflict resolution method is being added.
sequence_no | Number indicating when this conflict resolution method should be applied relative to other methods defined for the same column group or priority group.
method | The conflict resolution method. Valid values are, PRIORITY GROUP SITE PRIORITY USER FUNCTION or one of the methods in Table 16.14.
parameter_column_name | Comma-separated list of columns to be used to resolve the conflict (if VARCHAR2) or a PL/SQL table of column names. If column_group is passed, the column(s) passed to parameter_column_name must be in the group. A `*' indicates that all columns in the table or column group should be passed to the conflict resolution function, in alphabetical order.
priority_group | ADD_UPDATE_RESOLUTION only. If using a priority group or site priority group, the name of the group.
function_name | If designating a user-defined conflict resolution method, the name of the user function.
comment | Comment on the conflict resolution method, visible in the DBA_REPRESOLUTION data dictionary view.

#### 16.5.1.1.1 Exception

The ADD_<conflicttype>RESOLUTION procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
duplicatesequence | -1 | Resolution method already exists with sequence number sequence_no for this column or priority group
invalidmethod | -23340 | Resolution method method does not exist
invalidparameter | -23342 | Column(s) specified in parameter_column_name invalid
missingcolumn | -23334 | Specified column(s) do not exist in table oname
missingconstraint | -23344 | Constraint constraint_name specified in ADD_UNIQUE_RESOLUTION does not exist
missingfunction | -23341 | User-defined function function_name does not exist
missinggroup | -23331 | column_group does not exist
missingobject | -23308 | Table oname does not exist in the replication group
missingprioritygroup | -23336 | priority_group does not exist
nonmasterdef | -23312 | Calling site is not the master definition site
typefailure | -23319 | Datatype of one of the columns specified in parameter_column_name is not appropriate for the resolution method

#### 16.5.1.1.2 Restrictions

Note the following restrictions on calling ADD_<conflicttype>_RESOLUTION:

- You must call this procedure from the master definition site.

- After this call, you must generate replication support for the table passed to oname.

#### 16.5.1.1.3 Examples

The following examples illustrate how to assign various conflict resolution methods to replicated tables. These examples use the products table used in earlier examples; for convenience, we've included its description here again.

>     Sql>desc products
>     Name	                        Null?             Type
>      ---------------------  	---------	---
>      PRODUCT_ID                     NOT NULL	NUMBER(9)
>      PRODUCT_TYPE                   NOT NULL	NUMBER(6)
>      CATALOG_ID                     NOT NULL	VARCHAR2(15)
>      DESCRIPTION                    NOT NULL	VARCHAR2(30)
>      REV_LEVEL                      NOT NULL	VARCHAR2(15)
>      PRODUCTION_DATE                NOT NULL	DATE
>      PRODUCTION_STATUS              NOT NULL	VARCHAR2(12)
>      AUDIT_DATE                     NOT NULL	DATE
>      AUDIT_USER                     NOT NULL	VARCHAR2(30)
>      GLOBAL_NAME                    NOT NULL	VARCHAR2(20)

#### 16.5.1.1.4 Examples of ADD_UPDATE_RESOLUTION

Assume that we have created a priority group PG_PRODUCTION_STATUS and have designated priorities to the full range of values for the column PRODUCTION_STATUS. The following call implements this priority group as the conflict handler that Oracle invokes first (because sequence_no = 1) when an update conflict occurs.

>     BEGIN
>     	DBMS_REPCAT.ADD_UPDATE_RESOLUTION(
>     		sname		=> 'SPROCKET',
>     		oname		=> 'PRODUCTS',
>     		sequence_no	=>	1,
>     		method		=> 'PRIORITY GROUP',
>     		priority_group	=> 'PG_PRODUCTION_STATUS',
>     		comment		=> 'Update Res. 1 added on '||sysdate);
>     END;

This next call assigns the column group CG_PRODUCT_MFG_COLS as the second update conflict resolution handler for table products. Oracle invokes this resolution method if and only if the first method failed to resolve the conflict.

>     BEGIN
>     	DBMS_REPCAT.ADD_UPDATE_RESOLUTION(
>     		sname			=> 'SPROCKET',
>     		oname			=> 'PRODUCTS',
>     		colunn_group		=> 'CG_PRODUCT_PRICE_COLS',
>     		method			=> 'LATEST TIMESTAMP',
>     		parameter_column_name	=> 'PRODUCTION_DATE',
>     		comment			=> 'Update Res. 2 added on '||sysdate);
>     END;

The following example assigns a third update conflict resolution handler to the products table. This handler would simply ignore an update if the first two conflict handlers failed to resolve it.

>     BEGIN
>     	DBMS_REPCAT.ADD_UPDATE_RESOLUTION(
>     		sname			=> 'SPROCKET',
>     		oname			=> 'PRODUCTS',
>     		sequence_no		=> 3,
>     		method			=> 'DISCARD',
>     		comment			=> 'Update Res. 3 added on '||sysdate);
>     END;

#### 16.5.1.1.5 Examples of ADD_UNIQUE_RESOLUTION

Uniqueness conflicts may occur during inserts; for example, two different sites may insert a record with the same primary key. While you can guard against this sort of conflict by partitioning primary key values among your sites, this design is not always possible. Note that if you wish to use the APPEND SITE NAME or APPEND SEQUENCE NUMBER methods, the column with the unique constraint must specify a character datatype (CHAR or VARCHAR2). This choice of datatype may not be appropriate for a primary key column.

The following example configures the products table to discard records that result in uniqueness conflicts:

>     BEGIN
>     	DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
>     		sname			=> 'SPROCKET',
>     		oname			=> 'PRODUCTS',
>     		constraint_name		=> 'PK_PRODUCTS',
>     		sequence_no		=> 1,
>     		method			=> 'DISCARD',
>     		parameter_column	=> 'PRODUCT_ID',
>     		comment			=> 'Unique Res. 1 added on '||sysdate);
>     END;

#### 16.5.1.1.6 Examples of ADD_DELETE_RESOLUTION

As we have mentioned, Oracle does not provide any built-in conflict resolution techniques for delete conflicts. In fact, Oracle recommends that applications that use the advanced replication option avoid delete entirely, and simply use a status column to flag records as deleted. However, if you must delete rows, you can write your own conflict resolution method and assign it to your table. See the Section 16.5.1.1.7, "User-defined methods"" section later in this chapter.

The following function serves as a delete conflict handler for the products table. It forces a delete against the table.

>     	CREATE OR REPLACE FUNCTION products_delete_handler (
>     		old_product_id		  IN OUT NUMBER,
>     		Old_product_type	  IN OUT NUMBER,
>     		old_catalog_id	          IN OUT VARCHAR2,
>     		old_description		  IN OUT VARCHAR2,
>     		old_rev_level		  IN OUT VARCHAR2,
>     		old_production_date	  IN OUT DATE,
>     		old_production_status	  IN OUT VARCHAR2,
>     		old_audit_date		  IN OUT DATE,
>     		old_audit_user		  IN OUT VARCHAR2,
>     		old_global_name		  IN OUT VARCHAR2,
>     		ignore_discard_flag	  OUT BOOLEAN ) RETURN BOOLEAN IS

>     	BEGIN
>     		DELETE	FROM products
>     		WHERE	product_id = old_product_id;

>     		ignore_discard_flag := TRUE;

>     		RETURN TRUE;
>     	END products_delete_handler;

This final example designates the function products_delete_handler from the previous example and a user-defined delete conflict handler for the PRODUCTS_TABLE:

>     	DECLARE param_col_list DBMS_REPCAT.VARCHAR2S;
>     	BEGIN
>     		param_col_list( 1) := 'PRODUCT_ID';
>     		param_col_list( 2) := 'PRODUCT_TYPE';
>     		param_col_list( 3) := 'CATALOG_ID';
>     		param_col_list( 4) := 'DESCRIPTION';
>     		param_col_list( 5) := 'REV_LEVEL';
>     		param_col_list( 6) := 'PRODUCTION_DATE';
>     		param_col_list( 7) := 'PRODUCTION_STATUS',
>     		param_col_list( 8) := 'AUDIT_DATE',
>     		param_col_list( 9) := 'AUDIT_USER',
>     		param_col_list(10) := 'GLOBAL_NAME',

>     		DBMS_REPCAT.ADD_DELETE_RESOLUTION(
>     			sname			=> 'SPROCKET',
>     			oname			=> 'PRODUCTS',
>     			sequence_no		=> 1,
>     			paramekter_column_name	=> param_col_list,
>     			function_name		=> 'PRODUCTS_DELETE_HANDLER',
>     			comment			=> 'Del handler 1 added on ' ||  sysdate);
>     	END;

#### 16.5.1.1.7 User-defined methods

User-defined methods must meet the following criteria:

1. Must be a PL/SQL function.

2. Must return BOOLEAN TRUE if successful, FALSE otherwise.

3. Must not perform DDL.

4. Must not perform transaction control (e.g., ROLLBACK).

5. Must not perform session control (e.g., ALTER SESSION).

6. Must not perform system control (e.g. ALTER SYSTEM).

7. Update handlers accept old, new, and current column values for columns specified in parameter_column_name parameter of ADD_UPDATE_RESOLUTION. Old and current column values are IN parameters, new column values are IN OUT parameters.

8. Delete handlers accept old column values as IN parameters for all table columns.

9. Uniqueness handlers accept new values as IN OUT parameters for columns specified in the parameter_column_name parameter of ADD_UNIQUE_RESOLUTION.

10. Last parameter is ignore_discard_flag OUT BOOLEAN, which is set to TRUE if new values are to be discarded, or FALSE if they are to be accepted.

#### 16.5.1.2 The DBMS_REPCAT.DROP_<conflicttype>_RESOLUTION procedure

The DROP_<conflicttype>_RESOLUTION procedure removes a conflict resolution type from a table. The value of <conflicttype> can be UPDATE, UNIQUE, DELETE. Here are the specifications:

>     PROCEDURE DBMS_REPCAT.DROP_UPDATE_RESOLUTION
>        (sname IN VARCHAR2,
>         oname IN VARCHAR2,
>         column_group IN VARCHAR2,
>         sequence_no IN NUMBER) ;

>     PROCEDURE DBMS_REPCAT.DROP_UNIQUE_RESOLUTION
>        (sname IN VARCHAR2,
>         oname IN VARCHAR2,
>         constraint_name IN VARCHAR2,
>         sequence_no IN NUMBER) ;

>     PROCEDURE DBMS_REPCAT.DROP_DELETE_RESOLUTION
>        (sname IN VARCHAR2,
>         oname IN VARCHAR2,
>         sequence_no IN NUMBER) ;

Parameters are summarized in the following table.

Name | Description
--- | ---
sname | Name of the schema containing the replicated schema. Defaults to current user.
oname | Name of the replicated table.
column_group | Column group for which the conflict resolution method is defined.
constraint_name | For procedure DROP_UNIQUE_RESOLUTION only. Name of the constraint name or unique index for which the conflict resolution method is defined.
sequence_no | Number indicating when this conflict resolution method is applied relative to other conflict resolution methods defined for the same column group or priority group.

#### 16.5.1.2.1 Exceptions

The DROP_<conflicttype>_RESOLUTION procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
missingobject | -23308 | Table oname does not exist in the replication group
missingschema | -23306 | Schema sname does not exist
nonmasterdef | -23312 | Calling site is not the master definition site

#### 16.5.1.2.2 Restrictions

Note these restrictions on calling DROP_<conflicttype>_RESOLUTION:

- You must call this procedure from the master definition site.

- After this call, you must generate replication support for the table passed to oname.

#### 16.5.1.2.3 Example

In this example we drop the delete handler (created in a previous example) from the products table:

>     	BEGIN
>     		DBMS_REPCAT.DROP_DELETE_RESOLUTION(
>     			sname		=> 'SPROCKETS',
>     			oname		=> 'PRODUCTS',
>     			sequence_no	=> 1);
>     	END;

#### 16.5.1.3 The DBMS_REPCAT.COMMENT_ON_<conflicttype>_RESOLUTION procedure

You can use the COMMENT_ON_<conflicttype>_RESOLUTION procedure to create or replace a comment for a given resolution type. You can see this comment in the DBA_REPRESOLUTION data dictionary view. Following are the specifications for the three values of <conflicttype> (UPDATE, UNIQUE, DELETE):

>     PROCEDURE DBMS_REPCAT.COMMENT_ON_UPDATE_RESOLUTION
>        (sname IN VARCHAR2,
>         oname IN VARCHAR2,
>         column_group IN VARCHAR2,
>         sequence_no IN NUMBER,
>         comment IN VARCHAR2);

>     PROCEDURE DBMS_REPCAT.COMMENT_ON_UNIQUE_RESOLUTION
>        (sname IN VARCHAR2,
>         oname in VARCHAR2,
>         constraint_name IN VARCHAR2,
>         sequence_no IN NUMBER,
>         comment IN VARCHAR2) ;

>     PROCEDURE DBMS_REPCAT.COMMENT_ON_DELETE_RESOLUTION
>        (sname IN VARCHAR2,
>         oname IN VARCHAR2,
>         sequence_no IN NUMBER,
>         comment IN VARCHAR2) ;

Parameters are summarized in the following table.

Name | Description
--- | ---
sname | Name of the schema to which object oname belongs
oname | Name of the object
column_group | Name of column group for which conflict resolution method is defined
constraint_name | Name of unique constraint the method resolves (COMMENT_ON_UNIQUE_RESOLUTION only)
sequence_no | Sequence number associated with the resolution method
comment | Comment

#### 16.5.1.3.1 Exceptions

The COMMENT_ON_<conflicttype>_RESOLUTION procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
missingobject | -23308 | Object oname does not exist
missingresolution | -23343 | No resolution method exists for column_group and sequence_no
nonmasterdef | -23312 | Calling site is not the master definition site

#### 16.5.1.3.2 Restrictions

Note the following restrictions on calling COMMENT_ON_<conflicttype>_RESOLUTION:

- You must call this procedure from the master definition site.

- After this call, you must generate replication support for the table passed to oname.

#### 16.5.1.3.3 Example

This example replaces the comment on the unique resolution method created in a previous example:

>     	BEGIN
>     		DBMS_REPCAT.COMMENT_ON_UNIQUE_RESOLUTION(
>     			sname			=> 'SPROCKET',
>     			oname			=> 'PRODUCTS',
>     			constraint_name		=> 'PK_PRODUCTS',
>     			sequence_no		=> 1,
>     			comment			=> 'New comment added on '||sysdate);
>     	END;

16.4 Site Priority Groups with DBMS_REPCAT |  | 16.6 Monitoring Conflict Resolution with DBMS_REPCAT

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.