Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 16.4 Site Priority Groups with DBMS_REPCATChapter 16
Conflict Resolution
Next: 16.6 Monitoring Conflict Resolution with DBMS_REPCAT
 

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;


Previous: 16.4 Site Priority Groups with DBMS_REPCATOracle Built-in PackagesNext: 16.6 Monitoring Conflict Resolution with DBMS_REPCAT
16.4 Site Priority Groups with DBMS_REPCATBook Index16.6 Monitoring Conflict Resolution with DBMS_REPCAT

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference