B 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.example.com. 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.example.com. 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.

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;
/

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 sales.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
);

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 associative array 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_name  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
  -- 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;
/