|Oracle8i Replication Management API Reference
Release 2 (8.1.6)
Part Number A76958-01
User-Defined Conflict Resolution Methods, 2 of 4
Oracle allows 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, 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, Oracle stops evaluation of the method, and, if any other methods were provided to resolve the conflict with a later sequence number, Oracle does not evaluate them.
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 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.
For update conflicts, a user-defined function should accept the following values for each column in the column group:
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, 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, it indicates that the current column value should not be changed.
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, 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.
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, 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, 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.
You should avoid the following types of SQL statements in user-defined conflict resolution methods. Use of such statements can result in unpredictable results.
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.
-- User function similar to MAXIMUM method. -- If curr is null or curr < new, use new values. -- If new is null or new < curr, use current values. -- If both are null, no resolution. -- Does not converge with > 2 masters, unless -- always increasing. 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;
-- User function similar to ADDITIVE method. -- If old is null, old = 0. -- If new is null, new = 0. -- If curr is null, curr = 0. -- new = curr + (new - old) -> just like ADDITIVE method. 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;