If the conflict resolution routine raises an exception, Oracle stops evaluation of the routine, and, if any other routines were provided to resolve the conflict (with a later sequence number), Oracle does not evaluate them.
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. 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_CONFLICT procedures. The column parameters are passed to the conflict resolution routine in the order listed in the PARAMETER_COLUMN_NAME argument, or in ascending alphabetical order if you specified `*' for this argument. Where both old and new column values are passed as parameters (for update conflicts), the old value of the column immediately precedes the new value.
Attention: Type checking of parameter columns in user-defined conflict resolution routines is not performed until the DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT procedure generates and compiles the packages.
If the routine can resolve the conflict, it should modify the new column values so that the symmetric replication facility 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 your conflict resolution routine cannot guarantee convergence for uniqueness conflicts, your routine should include a notification mechanism.
If the conflict resolution routine can resolve the conflict, it modifies the old column values so that the symmetric replication facility 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 will raise a NO_DATA_FOUND exception and the transaction will be placed in the error table.
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, as described .
-- 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;