Conflict Resolution, 4 of 6

Create Conflict Resolution Methods for Uniqueness Conflicts

In a replicated environment, you may encounter situations where you receive a conflict on a unique constraint, often resulting from an insert. If your business rules allow you to delete the duplicate row, you can define such resolution with Oracle's pre-built conflict resolution methods.

More often, however, you probably want to modify the conflicting value so that it no longer violates the unique constraint. Modifying the conflicting value ensures that you do not lose important data. Oracle's pre-built uniqueness conflict resolution method can make the conflicting value unique by appending a site name or a sequence number to the value.

An additional component that accompanies uniqueness conflict resolution methods is a notification facility. The conflicting information is modified by Oracle so that it can be inserted into the table, but you should be notified so that you can analyze the conflict to determine whether the record should be deleted, or the data merged into another record, or a completely new value be defined for the conflicting data.

--The following procedures need to be executed by the replication administrator.

CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      GNAME => 'scott_mg');
END;
/

--As you might expect, a uniqueness conflict resolution method detects and
--resolves conflicts encountered on columns with a UNIQUE constraint. Use
--the ALTER_MASTER_REPOBJECT procedure (described) to add
--a UNIQUE constraint to the EMP table.

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'SCOTT',
      oname => 'EMP',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE scott.emp ADD 
                   (constraint emp_ename_unique UNIQUE(ename))');
END;
/

--After you have added the UNIQUE constraint to your replicated table,
--make sure that you regenerate replication support for
--the affected table. This step should be performed immmediately
--after you alter the replicated object.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'EMP', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

--The following table (conf_report) stores the messages received from
--your notification facility.

BEGIN
   DBMS_REPCAT.EXECUTE_DDL(
      GNAME => 'scott_mg',
      DDL_TEXT => 'CREATE TABLE scott.conf_report (
                   line NUMBER(2),
                   txt VARCHAR2(80),
                   timestamp DATE,
                   table_name VARCHAR2(30),
                   table_owner VARCHAR2(30),
                   conflict_type VARCHAR2(7))');
END;
/

CONNECT scott/tiger@orc1.world

--The following package (notify) sends a notification to the CONF_REPORT
--table when a conflict is detected.

--The conflict resolution notification package that is created in this script is
--described in detail in Appendix B, "User-Defined Conflict Resolution Methods".

CREATE OR REPLACE PACKAGE notify AS
   FUNCTION emp_unique_violation (ename IN OUT VARCHAR2,
      discard_new_values IN OUT BOOLEAN)
   RETURN BOOLEAN;
END notify;
/

CREATE OR REPLACE PACKAGE BODY notify AS
   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 scott.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;
   FUNCTION emp_unique_violation(ename IN OUT VARCHAR2,
      discard_new_values IN OUT BOOLEAN)
   RETURN BOOLEAN IS
      local_node VARCHAR2(128);
      conf_report MESSAGE_TABLE;
      conf_time DATE := SYSDATE;
   BEGIN
      BEGIN
         SELECT global_name INTO local_node FROM global_name;
      EXCEPTION WHEN others THEN local_node := '?';
      END;
      conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN TABLE EMP 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 SITE NAME METHOD';
      conf_report(4) := 'ENAME: ' || ename;
      conf_report(5) := NULL;
      report_conflict(conf_report, 5, conf_time, 'EMP', 'SCOTT', 'UNIQUE');
      discard_new_values := FALSE;
      RETURN FALSE;
   END emp_unique_violation;
END notify;
/

CONNECT repadmin/repadmin@orc1.world

--The following package is replicated to all of the master sites in your
--replication environment, which ensures that the notification facility is
--available at all master sites.

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'SCOTT_MG',
      type =>  'PACKAGE',
      oname => 'notify',
      sname => 'SCOTT');
END;
/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'SCOTT_MG',
      type =>  'PACKAGE BODY',
      oname => 'notify',
      sname => 'SCOTT');
END;
/

--After you have completed building your notification facility, add the 
--notification facility as one of your conflict resolution methods, 
--even though it only notifies of a conflict. The following API example
--demonstrates adding the notification facility as a USER FUNCTION.

BEGIN
   DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
      SNAME => 'scott', 
      ONAME => 'emp', 
      CONSTRAINT_NAME => 'emp_ename_unique',
      SEQUENCE_NO => 1, 
      METHOD => 'USER FUNCTION',
      COMMENT => 'Notify DBA',
      PARAMETER_COLUMN_NAME => 'ename',
      FUNCTION_NAME => 'scott.notify.emp_unique_violation'); 
END;
/

--After you have added the notification facility, you are ready to add the
--actual conflict resolution method to your table. The following API example
--demonstrates adding the APPEND SITE NAME uniqueness conflict resolution
--method to your replicated table.

BEGIN
   DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
      SNAME => 'scott',
      ONAME => 'emp',
      CONSTRAINT_NAME => 'emp_ename_unique',
      SEQUENCE_NO => 2,
      METHOD => 'APPEND SITE NAME',
      PARAMETER_COLUMN_NAME => 'ename');
END;
/

--After you have defined your conflict resolution methods, regenerate 
--replication support for the table that received the conflict
--resolution methods.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'emp', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

--After replication support has been regenerated, resume replication
--activity by using the RESUME_MASTER_ACTIVITY procedure API.

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/