Advanced Replication Conflict Resolution

Data conflicts can occur when using Advanced Replication - for example, if the same record is updated from multiple sites. Oracle allows conflict resolution rules to be defined to report and/or resolve such conflicts automatically.

Define conflict handlers

Update resolution

create function upd_handler (
  old_userid          IN     NUMBER,
  new_userid          IN OUT NUMBER,
  curr_userid         IN     NUMBER,
  ignore_discard_flag OUT    BOOLEAN)
RETURN BOOLEAN AS
BEGIN
  mail('Update conflict: old userid = '||old_userid||', new = '||new_userid||', curr = '||curr_userid);
  ignore_discard_flag := FALSE;
  return FALSE;
END;
/

Delete resolution

create function del_handler (
  old_userid          IN  NUMBER,
  ignore_discard_flag OUT BOOLEAN)
RETURN BOOLEAN AS
BEGIN
  mail('Delete conflict: userid = '||old_userid);
  ignore_discard_flag := FALSE;
  return FALSE;
END;
/

Unuique resolution

create function ins_handler (
  new_userid          IN   NUMBER,
  ignore_discard_flag  OUT BOOLEAN)
RETURN BOOLEAN AS
BEGIN
  mail('Uniqueness conflict: userid = '||new_userid);
  ignore_discard_flag := FALSE;
  return FALSE;
END;
/

Install conflict handlers

Before isntalling the below handlers, suspend master activity:

exec dbms_repcat.suspend_master_activity(gname=>'MYGRP');

When done, generate support and resume:

exec dbms_repcat.generate_replication_support('SCOTT', 'USERS', 'TABLE');
exec dbms_repcat.resume_master_activity(gname=>'MYGRP');

Update resolution

exec dbms_repcat.drop_update_resolution('SCOTT', 'USERS', 'CG_USERS', 1);
exec dbms_repcat.drop_column_group('SCOTT', 'USERS', 'CG_USERS');
exec dbms_repcat.make_column_group('SCOTT', 'USERS', 'CG_USERS', '*');
exec dbms_repcat.add_update_resolution(                                  -
                           'SCOTT', 'USERS', 'CG_USERS',                 -
                           sequence_no   => 1,                           -
                           method        => 'USER FUNCTION',             -
                           function_name => 'upd_handler',               -
                           parameter_column_name => 'userid');

Delete resolution

exec dbms_repcat.drop_delete_resolution('SCOTT', 'USERS', 1);
exec dbms_repcat.add_delete_resolution(                                   -
                            'SCOTT', 'USERS',                             -
                            sequence_no   => 1,                           -
                            method        => 'USER FUNCTION',             -
                            function_name => 'del_handler',               -
                            parameter_column_name => 'userid');

Unuique resolution

exec dbms_repcat.drop_unique_resolution('SCOTT', 'USERS', 'PKUSERS', 1);
exec dbms_repcat.add_unique_resolution(                                   -
                            'SCOTT', 'USERS', 'PKUSERS',                  -
                            sequence_no   => 1,                           -
                            method        => 'USER FUNCTION',             -
                            function_name => 'ins_handler',               -
                            parameter_column_name => 'userid');