16.5.1.1.1 Exception

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

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.1 Exceptions

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

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.1 Exceptions

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

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;