B
User-Defined Conflict Resolution Methods

This appendix describes how to build user-defined conflict resolution methods and user-defined conflict notification methods.

This appendix contains these topics:

• User-Defined Conflict Resolution Methods
• User-Defined Conflict Notification Methods
• Viewing Conflict Resolution Information

User-Defined Conflict Resolution Methods

Oracle enables you to write your own conflict resolution or notification methods. A user-defined conflict resolution method is a PL/SQL function that returns either TRUE or FALSE. TRUE indicates that the method has successfully resolved all conflicting modifications for a column group. If the method cannot successfully resolve a conflict, then it should return FALSE. Oracle continues to evaluate available conflict resolution methods, in sequence order, until either a method returns TRUE or there are no more methods available.

If the conflict resolution method raises an exception, then Oracle stops evaluation of the method, and, if any other methods were provided to resolve the conflict with a later sequence number, then Oracle does not evaluate them.

Conflict Resolution Method Parameters

The parameters needed by a user-defined conflict resolution method are determined by the type of conflict being resolved (uniqueness, update, or delete) and the columns of the table being replicated. All conflict resolution methods take some combination of old, new, and current column values for the table.

• The old value represents the value of the row at the initiating site before you made the change.
• The new value represents the value of the row at the initiating site after you made the change.
• The current value represents the value of the equivalent row at the receiving site.

  Note: Recall that Oracle uses the primary key, or the key specified by SET_COLUMNS, to determine which rows to compare.

The conflict resolution function should accept as parameters the values for the columns specified in the PARAMETER_COLUMN_NAME argument to the DBMS_REPCAT.ADD_conflicttype_RESOLUTION procedures. The column parameters are passed to the conflict resolution method in the order listed in the PARAMETER_COLUMN_NAME argument, or in ascending alphabetical order if you specified '*' for this argument. When both old and new column values are passed as parameters (for update conflicts), the old value of the column immediately precedes the new value.

Resolving Update Conflicts

For update conflicts, a user-defined function should accept the following values for each column in the column group:

• Old column value from the initiating site. The mode for this parameter is IN. This value should not be changed.
• New column value from the initiating site. The mode for this parameter is IN OUT. If the function can resolve the conflict successfully, then it should modify the new column value as needed.
• Current column value from the receiving site. The mode for this parameter is IN.

The old, new, and current values for a column are received consecutively. The final argument to the conflict resolution method should be a Boolean flag. If this flag is false, then it indicates that you have updated the value of the IN OUT parameter (new) and that you should update the current column value with this new value. If this flag is true, then it indicates that the current column value should not be changed.

Resolving Uniqueness Conflicts

Uniqueness conflicts can occur as the result of an INSERT or UPDATE. Your uniqueness conflict resolution method should accept the new column value from the initiating site in IN OUT mode for each column in the column group. The final parameter to the conflict resolution method should be a Boolean flag.

If the method can resolve the conflict, then it should modify the new column values so that Oracle can insert or update the current row with the new column values. Your function should set the Boolean flag to true if it wants to discard the new column values, and false otherwise.

Because a conflict resolution method cannot guarantee convergence for uniqueness conflicts, a user-defined uniqueness resolution method should include a notification mechanism.

Resolving Delete Conflicts

Delete conflicts occur when you successfully delete from the local site, but the associated row cannot be found at the remote site (for example, because it had been updated). For delete conflicts, the function should accept old column values in IN OUT mode for the entire row. The final parameter to the conflict resolution method should be a Boolean flag.

If the conflict resolution method can resolve the conflict, then it modifies the old column values so that Oracle can delete the current row that matches all old column values. Your function should set the Boolean flag to true if it wants to discard these column values, and false otherwise.

If you perform a delete at the local site and an update at the remote site, then the remote site detects the delete conflict, but the local site detects an unresolvable update conflict. This type of conflict cannot be handled automatically. The conflict raises a NO_DATA_FOUND exception and Oracle logs the transaction as an error transaction.

Designing a mechanism to properly handle these types of update/delete conflicts is difficult. It is far easier to avoid these types of conflicts entirely, by simply "marking" deleted rows, and then purging them using procedural replication.

Multitier Materialized Views and User-Defined Conflict Resolution Methods

When you use user-defined conflict resolution methods with multitier materialized views, the information about these methods is pulled down to the master materialized view sites automatically. This information is stored in the data dictionary at the master materialized view site. However, the user-defined conflict resolution methods themselves cannot be pulled down from the master site. Therefore, you must re-create these methods at the master materialized view site.

Restrictions for User-Defined Conflict Resolution Methods

The following sections describe restrictions for user-defined conflict resolution methods.

SQL Statement Restrictions for User-Defined Conflict Resolution Methods

Avoid the following types of SQL statements in user-defined conflict resolution methods. Use of such statements can result in unpredictable results.

• Data definition language (DDL) statements (such as CREATE, ALTER, DROP)
• Transaction control statements (such as COMMIT, ROLLBACK)
• Session control (such as ALTER SESSION)
• System control (such as ALTER SYSTEM)

Column Subsetting Restrictions for User-Defined Conflict Resolution Methods

Avoid subsetting the columns in a column group when you create updatable multitier materialized views. Column subsetting excludes columns that are in master tables or master materialized views from a materialized view based on these masters. You do this by specifying certain select columns in the SELECT statement during materialized view creation.

When you use conflict resolution with multitier materialized views, you cannot define the conflict resolution methods at the materialized view site. Conflict resolution methods are always pulled down from the master site. Therefore, if you subset the columns in a column group that has a user-defined conflict resolution applied to it, the conflict resolution method will not be able to find all of the columns in the column group at a master materialized view site. When this happens, the conflict resolution method returns the following error:

ORA-23460 missing value for column in resolution method

For example, consider a case where the job_id, salary, and commission_pct columns in the hr.employees table are part of a column group name employees_cg1 that has a user-defined conflict resolution method applied to it at the master site hq.world. To protect the privacy of your sales staff, you create a level 1 updatable materialized view that uses column subsetting to exclude the salary and commission_pct columns at the ca.us office. When you create this materialized view at the ca.us office, the conflict resolution method is pulled down from hq.world. You then create an updatable multitier materialized view at the sf.ca office based on the level 1 materialized view at the ca.us office.

Given this replication environment, if a conflict arises for a job_id value at the level 1 materialized view at the ca.us office, then the conflict resolution method fails to find the salary and commission_pct columns and returns the ORA-23460 error mentioned previously.

Examples of User-Defined Conflict Resolution Method

The following examples show user-defined methods that are variations on the standard maximum and additive prebuilt conflict resolution methods. Unlike the standard methods, these custom functions can handle nulls in the columns used to resolve the conflict.

Maximum User Function

-- User function similar to MAXIMUM method.
-- If curr is null or curr < new, then use new values.
-- If new is null or new < curr, then use current values.
-- If both are null, then no resolution.
-- Does not converge with > 2 masters, unless 
-- always increasing.

CREATE OR REPLACE FUNCTION max_null_loses(old  IN       NUMBER,
                        new                    IN  OUT  NUMBER,
                        cur                    IN       NUMBER,
                        ignore_discard_flag    OUT      BOOLEAN)
  RETURN BOOLEAN IS
BEGIN
   IF (new IS NULL AND cur IS NULL) OR new = cur THEN
       RETURN FALSE;
   END IF;
   IF new IS NULL THEN
       ignore_discard_flag := TRUE;
   ELSIF cur IS NULL THEN
       ignore_discard_flag := FALSE;
   ELSIF new < cur THEN
       ignore_discard_flag := TRUE;
   ELSE
       ignore_discard_flag := FALSE;
   END IF;
   RETURN TRUE;
END max_null_loses;
/

Additive User Function

-- User function similar to ADDITIVE method.
-- If old is null, then old = 0.
-- If new is null, then new = 0.
-- If curr is null, then curr = 0.
-- new = curr + (new - old) -> just like ADDITIVE method.

CREATE OR REPLACE FUNCTION additive_nulls(old  IN       NUMBER,
                        new                    IN  OUT  NUMBER,
                        cur                    IN       NUMBER,
                        ignore_discard_flag    OUT      BOOLEAN)
   RETURN BOOLEAN IS
   old_val NUMBER := 0.0;
   new_val NUMBER := 0.0;
   cur_val NUMBER := 0.0;
BEGIN
   IF old IS NOT NULL THEN
      old_val := old;
   END IF;
   IF new IS NOT NULL THEN
      new_val := new;
   END IF;
   IF cur IS NOT NULL THEN
      cur_val := cur;
   END IF;
   new := cur_val + (new_val - old_val);
   ignore_discard_flag := FALSE;
   RETURN TRUE;
END additive_nulls;
/

User-Defined Conflict Notification Methods

A conflict notification method is a user-defined function that provides conflict notification rather than or in addition to conflict resolution. For example, you can write your own conflict notification methods to log conflict information in a database table, send an email message, or page an administrator. After you write a conflict notification method, you can assign it to a column group (or constraint) in a specific order so that Oracle notifies you when a conflict happens, before attempting subsequent conflict resolution methods, or after Oracle attempts to resolve a conflict but cannot do so.

To configure a replicated table with a user-defined conflict notification mechanism, you must complete the following steps:

Step 1 Create a conflict notification log.

Step 2 Create the user-defined conflict notification method in a package.

The following sections explain each step.

Creating a Conflict Notification Log

When configuring a replicated table to use a user-defined conflict notification method, the first step is to create a database table that can record conflict notifications. You can create a table to log conflict notifications for one or many tables in a master group.

To create a conflict notification log table at all master sites, use the replication execute DDL facility. For more information, see "EXECUTE_DDL Procedure". Do not generate replication support for the conflict notification tables because their entries are specific to the site that detects a conflict.

Sample Conflict Notification Log Table

The following CREATE TABLE statement creates a table that you can use to log conflict notifications from several tables in a master group.

CREATE TABLE conf_report (
 line          NUMBER(2),    --- used to order message text
 txt           VARCHAR2(80), --- conflict notification message
 timestamp     DATE,         --- time of conflict
 table_name    VARCHAR2(30), --- table in which the 
                             --- conflict occurred
 table_owner   VARCHAR2(30), --- owner of the table
 conflict_type VARCHAR2(6)   --- INSERT, DELETE or UNIQUE
);

Creating a Conflict Notification Package

To create a conflict notification method, you must define the method in a PL/SQL package and then replicate the package as part of a master group along with the associated replicated table.

A conflict notification method can perform conflict notification only, or both conflict notification and resolution. If possible, you should always use one of Oracle's prebuilt conflict resolution methods to resolve conflicts. When a user-defined conflict notification method performs only conflict notification, assign the user-defined method to a column group (or constraint) along with conflict resolution methods that can resolve conflicts.

Sample Conflict Notification Package

The following package and package body perform a simple form of conflict notification by logging uniqueness conflicts for a CUSTOMERS table into the previously defined CONF_REPORT table.

CREATE OR REPLACE PACKAGE notify AS
 -- Report uniqueness constraint violations on CUSTOMERS table
 FUNCTION customers_unique_violation (
   first_name         IN OUT VARCHAR2,
   last_name          IN OUT VARCHAR2,
   discard_new_values IN OUT BOOLEAN) 
 RETURN BOOLEAN;
END notify;
/

CREATE OR REPLACE PACKAGE BODY notify AS
 -- Define a PL/SQL index-by table to hold the notification message
 TYPE message_table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
 PROCEDURE report_conflict (
   conflict_report IN MESSAGE_TABLE,
   report_length   IN NUMBER,
   conflict_time   IN DATE,
   conflict_table  IN VARCHAR2,
   table_owner     IN VARCHAR2,
   conflict_type   IN VARCHAR2) IS
 BEGIN
   FOR idx IN 1..report_length LOOP
     BEGIN
       INSERT INTO sales.conf_report 
         (line, txt, timestamp, table_name, table_owner, conflict_type)
         VALUES (idx, SUBSTR(conflict_report(idx),1,80), conflict_time,
                 conflict_table, table_owner, conflict_type);
     EXCEPTION WHEN others THEN NULL;
     END;
   END LOOP;
 END report_conflict;
 -- This is the conflict resolution method that is called first when
 -- a uniqueness constraint violated is detected in the CUSTOMERS table.
 FUNCTION customers_unique_violation (
   first_name  IN OUT VARCHAR2,
   last_nameIN OUT VARCHAR2,
   discard_new_valuesIN OUT BOOLEAN) 
  RETURN BOOLEAN IS
   local_node  VARCHAR2(128);
   conf_report MESSAGE_TABLE;
   conf_time   DATE := SYSDATE;
  BEGIN
  -- Get the global name of the local site
    BEGIN
      SELECT global_name INTO local_node FROM global_name;
    EXCEPTION WHEN others THEN local_node := '?';
    END;
  -- Generate a message for the DBA
  conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN TABLE CUSTOMERS ON ' ||
                    TO_CHAR(conf_time, 'MM-DD-YYYY HH24:MI:SS');
  conf_report(2) := '  AT NODE ' || local_node;
  conf_report(3) := 'ATTEMPTING TO RESOLVE CONFLICT USING ' ||
                    'APPEND SEQUENCE METHOD';
  conf_report(4) := 'FIRST NAME: ' || first_name;
  conf_report(5) := 'LAST NAME:  ' || last_name;
  conf_report(6) := NULL;
  --- Report the conflict
  report_conflict(conf_report, 5, conf_time, 'CUSTOMERS',  
                'OFF_SHORE_ACCOUNTS', 'UNIQUE');
  --- Do not discard the new column values. They are still needed by
  --- other conflict resolution methods.
  discard_new_values := FALSE;
  --- Indicate that the conflict was not resolved.
    RETURN FALSE;
  END customers_unique_violation;
END notify;
/

Viewing Conflict Resolution Information

Oracle provides replication catalog (REPCAT) views that you can use to determine what conflict resolution methods are being used by each of the tables and column groups in your replication environment. Each view has three versions: USER_*, ALL_*, SYS.DBA_*. The available views are shown in the following table.

View	Description
ALL_REPRESOLUTION_METHOD	Lists all of the available conflict resolution methods.
ALL_REPCOLUMN_GROUP	Lists all of the column groups defined for the database.
ALL_REPGROUPED_COLUMN	Lists all of the columns in each column group in the database.
ALL_REPPRIORITY_GROUP	Lists all of the priority groups and site priority groups defined for the database.
ALL_REPPRIORITY	Lists the values and corresponding priority levels for each priority or site priority group.
ALL_REPCONFLICT	Lists the types of conflicts (delete, update, or uniqueness) for which you have specified a resolution method, for the tables, column groups, and unique constraints in the database.
ALL_REPRESOLUTION	Shows more specific information about the conflict resolution method used to resolve conflicts on each object.
ALL_REPPARAMETER_COLUMN	Shows which columns are used by the conflict resolution methods to resolve a conflict.