This appendix describes how to build user-defined conflict resolution methods and user-defined conflict notification methods.
This appendix contains these topics:
Oracle enables 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, then 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, then Oracle stops evaluation of the method, and, if any other methods were provided to resolve the conflict with a later sequence number, then 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 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.
Note:
Recall that Oracle uses the primary key, or the key specified bySET_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
_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:
Old column value from the initiating site. The mode for this parameter is IN
. This value should not be changed.
New column value from the initiating site. The mode for this parameter is IN
OUT
. If the function can resolve the conflict successfully, then it should modify the new column value as needed.
Current column value from the receiving site. The mode for this parameter is IN
.
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
, then 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
, then 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, then 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, then 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, then 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.
When you use user-defined conflict resolution methods with multitier materialized views, the information about these methods is pulled down to the master materialized view sites automatically. This information is stored in the data dictionary at the master materialized view site. However, the user-defined conflict resolution methods themselves cannot be pulled down from the master site. Therefore, you must re-create these methods at the master materialized view site.
See Also:
"Viewing Conflict Resolution Information" for information about the data dictionary views that store information about user-defined conflict resolution methods
Oracle Database Advanced Replication for more information about conflict resolution and multitier materialized views
The following sections describe 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
)
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.world
. 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.world
. 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.
See Also:
Oracle Database Advanced Replication for more information about column subsettingThe 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, 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; /
-- 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; /
A conflict notification method is a user-defined function that provides conflict notification rather than or in addition to conflict resolution. For example, you can write your own conflict notification methods to log conflict information in a database table, send an email message, or page an administrator. After you write a conflict notification method, you can assign it to a column group (or constraint) in a specific order so that Oracle notifies you when a conflict happens, before attempting subsequent conflict resolution methods, or after Oracle attempts to resolve a conflict but cannot do so.
To configure a replicated table with a user-defined conflict notification mechanism, you must complete the following steps:
Create a conflict notification log.
Create the user-defined conflict notification method in a package.
The following sections explain each step.
When configuring a replicated table to use a user-defined conflict notification method, the first step is to create a database table that can record conflict notifications. You can create a table to log conflict notifications for one or many tables in a master group.
To create a conflict notification log table at all master sites, use the replication execute DDL facility. For more information, see "EXECUTE_DDL Procedure". Do not generate replication support for the conflict notification tables because their entries are specific to the site that detects a conflict.
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 );
To create a conflict notification method, you must define the method in a PL/SQL package and then replicate the package as part of a master group along with the associated replicated table.
A conflict notification method can perform conflict notification only, or both conflict notification and resolution. If possible, you should always use one of Oracle's prebuilt conflict resolution methods to resolve conflicts. When a user-defined conflict notification method performs only conflict notification, assign the user-defined method to a column group (or constraint) along with conflict resolution methods that can resolve conflicts.
Note:
If Oracle cannot ultimately resolve a replication conflict, then Oracle rolls back the entire transaction, including any updates to a notification table. If notification is necessary independent of transactions, then you can design a notification mechanism to use the OracleDBMS_PIPES
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.
Note:
This example of conflict notification does not resolve any conflicts. You should either provide a method to resolve conflicts (such as discard or overwrite), or provide a notification mechanism that will succeed (for example, using e-mail) even if the error is not resolved and the transaction is rolled back. With simple modifications, the following user-defined conflict notification method can take more active steps. For example, instead of just recording the notification message, the package can use theDBMS_OFFICE
utility package to send an Oracle Office email message to an administrator.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 index-by table 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; /
Oracle provides replication catalog (REPCAT) views that you can use to determine what conflict resolution methods are being used by each of the tables and column groups in your replication environment. Each view has three versions: USER_*
, ALL_*
, SYS.DBA_*
. The available views are shown in the following table.
View | Description |
---|---|
ALL_REPRESOLUTION_METHOD | Lists all of the available conflict resolution methods. |
ALL_REPCOLUMN_GROUP | Lists all of the column groups defined for the database. |
ALL_REPGROUPED_COLUMN | Lists all of the columns in each column group in the database. |
ALL_REPPRIORITY_GROUP | Lists all of the priority groups and site priority groups defined for the database. |
ALL_REPPRIORITY | Lists the values and corresponding priority levels for each priority or site priority group. |
ALL_REPCONFLICT | Lists the types of conflicts (delete, update, or uniqueness) for which you have specified a resolution method, for the tables, column groups, and unique constraints in the database. |
ALL_REPRESOLUTION | Shows more specific information about the conflict resolution method used to resolve conflicts on each object. |
ALL_REPPARAMETER_COLUMN | Shows which columns are used by the conflict resolution methods to resolve a conflict. |
See Also:
Chapter 23, "Replication Catalog Views"