SharePlex 12.0 - Administration Guide

User defined conflict resolution routines for Oracle to Oracle

To create conflict resolution routines, you write PL/SQL procedures that direct the action of SharePlex when a conflict occurs. Business rules vary widely from company to company, so it is impossible to create a standard set of conflict resolution rules and syntax that apply in every situation. You will probably need to write your own routines. It is good practice to write more than one procedure, such as making site or system priority the primary routine and timestamp a secondary routine. SharePlex invokes one routine after another until one succeeds or there are no more procedures available.

SharePlex provides the following tools that can be used as a basis for your routines:

• A generic PL/SQL interface that you can use to write basic routines based on DML operation types. For more information, see How to write a routine using the SharePlex generic interface.
• Packaged routines that perform basic conflict resolution based on a key or column value, which can be used as a backup measure in case the custom routines fail. For more information, see User defined conflict resolution routines for Oracle to Oracle.

How to write a routine using the SharePlex generic interface

SharePlex provides a generic conflict resolution PL/SQL package that can be used to pass information to and from the procedural routines that you write.

Note: Custom routines are supported for Oracle to Oracle source-target combinations only.

Before you get started, understand the following guidelines:

• The same PL/SQL package is used for both generic conflict resolution and transformation (its name is sp_cr). Use either generic conflict resolution or transformation for a table, but not both. Transformed tables cannot be compared by SharePlex and conflict resolution cannot succeed. If both are used, SharePlex only calls the transformation routine. If appropriate, you can use generic conflict resolution and transformation for different tables in the same configuration. For more information, see Configure Data Transformation.
• Conflict resolution cannot be used for DDL changes.
• Any table to be accessed through PL/SQL for conflict resolution requires implicitly granted privileges from the owner of the object to SharePlex.
• Conflict resolution does not support changes to LONG or LOB columns.

Note: If you ran the SharePlex conflict resolution demonstration in the SharePlex Installation and Setup Guide, you can view a sample generic conflict resolution routine by viewing the od_employee_gen routine that was installed in the database used for the demonstration.

Procedure interface

Follow this template to create your procedure.

where:

• splex is the SharePlex schema.
• sp_cr is the name of the package that contains the PL/SQL record and table structures.
• row_typ is the name of the PL/SQL record that passes in/out variables (see Package definition).
• col_def_type is the name of the PL/SQL table that stores column information (see col_def_type table).

Package definition

SharePlex defines PL/SQL record and table structures in a public package named sp_cr in the SharePlex database schema. The package uses the following parameters.

CREATE or REPLACE PACKAGE sp_cr AS 

TYPE row_typ IS RECORD 

src_host VARCHAR2(32), 

src_ora_sid VARCHAR2(32), 

src_ora_time VARCHAR2(20), 

source_rowid VARCHAR2(20), 

target_rowid VARCHAR2(20), 

statement_type VARCHAR2(6), 

source_table VARCHAR2(78), 

target_table VARCHAR2(78), 

oracle_err NUMBER, 

status NUMBER, 

action NUMBER, 

reporting NUMBER 

); 

IN variables

For each row operation that causes a conflict, SharePlex passes this metadata information to your procedure.

OUT variables

These variables direct the action of SharePlex based on whether the procedure succeeded or failed).

col_def_type table

SharePlex creates a col_def_tabtyp PL/SQL table for each replicated operation. This table stores column information. It is different depending on whether the procedure is used for transformation or conflict resolution.

• Transformation: For each row operation, SharePlex writes column information to col_def_type.
• Conflict resolution: For each row operation that causes a conflict, SharePlex writes column information to col_def_tabtyp.

All fields are passed by SharePlex to your routine, although not all will have values if SharePlex cannot locate the row.

Following is the data type that is used to populate the col_def_tabtyp table.

type col_def_typ is record 

(column_name user_tab_columns.column_name%type 

,data type user_tab_columns.data type%type

,is_key boolean 

,is_changed boolean 

,old_value varchar2(32764) 

,new_value varchar2(32764) 

,current_value varchar2(32764) 

); 

type col_def_tabtyp is table of col_def_typ

Description of col_def_tabtyp

Example entries in col_def_tabtyp table per operation type

The following tables illustrate the possible outcomes of each type of operation.

INSERT operation

¹ When an INSERT fails, it is because a row with the same PRIMARY key already exists in the target database. SharePlex does not return the current value for INSERTs.

UPDATE operation

¹ (Conflict resolution) When an UPDATE fails, it is because SharePlex cannot find the row by using the PRIMARY key and the pre-image. If the row cannot be found, SharePlex searches for the row by using only the PRIMARY key. If SharePlex finds the row, it returns the current value for the key column as well as the changed columns. If SharePlex cannot find the row by using just the PRIMARY key, then SharePlex returns a NULL.

² (Transformation) For an UPDATE, SharePlex cannot locate a row using the PRIMARY key and the pre-images, because the pre-images are different due to transformation. As an alternative, it searches for the row using just the PRIMARY key. If it finds it, SharePlex returns the current value for the key column as well as the changed columns. If it cannot locate the row using just the PRIMARY key, then current_value is NULL

DELETE operation

¹ When a DELETE fails, it is because SharePlex could not find the row by using the PRIMARY key. Therefore, SharePlex returns a NULL.

List the routines in conflict_resolution.SID

After you create the conflict resolution procedure(s), construct the conflict resolution file. This file tells SharePlex which procedures to use for which objects and operation types, and in which order.

Where to find the conflict resolution file

A blank conflict_resolution.SID file, where SID is the ORACLE_SID of the target instance, was included in the data sub-directory of the SharePlex variable-data directory when SharePlex was installed. Use the file on the target system.

If this file does not exist, you can create one in ASCII format in an ASCII text editor. It must be named conflict_resolution.SID, where SID is the ORACLE_SID of the target instance.

Note: the SID is case-sensitive.

Important! There can be only one conflict_resolution.SID file per active configuration.

How to make entries in the conflict resolution file

Use the following template to link a procedure to one or more objects and operation types.

where:

• owner.object is the owner and name of a target object, or a wildcarded entry. (See Syntax rules)
• i| u | d is the type of operation that creates the conflict that is resolved with the specified procedure. You can specify any or all operation types, for example id or iud. Upper or lower case are both valid.
• owner.procedure is the owner and name of the conflict resolution procedure that will handle the specified object and operation type.

Syntax rules

• There must be at least one space between the object specification, the operation type specification, and the procedure specification.
• You can use the LIKE operator and a SQL wildcard (%) to specify multiple objects by using a search string. (See the Example.)
• You can use an underscore (_) to denote a single-character wildcard. For table names that contain an underscore character (for example emp_sal), SharePlex recognizes the backslash (\) as an escape character to denote the underscore as a literal and not a wildcard, for example: like:scott.%\_corp\_emp. If you are not using the LIKE operator, the backslash escape character is not required if an object name contains an underscore.
• The order in which you list the procedures in the conflict resolution file determines their priority of use (in descending order). If you list a table-specific procedure, SharePlex uses it before procedures that are specified with a wildcarded object name.
• You a comment line anywhere in the file. Start a comment line with the pound symbol (#).

Example conflict resolution file

How it works:

• The scott.sal_cr routine is used for the scott.sal table before the scott.emp_cr1 procedure is used for that table.
• The scott.emp_cr1 procedure is used before the scott.emp_cr2 procedure for all tables meeting the search criteria, and so forth.
• For scott.cust, a procedure is called for UPDATEs before the other routines are used for all operations.

How to specify SharePlex prepared routines in the conflict resolution file

To use the SharePlex prepared routines for all tables in the replication configuration, use the !DEFAULT parameter instead of specifying an owner and object name.

A custom routine takes priority over a SharePlex prepared routine. A prepared routine is used only if the custom routine fails. This is true regardless of the order in which the !DEFAULT-associated routine appears in the file.

The !DEFAULT parameter is case-sensitive. It must be typed in all capital letters.

In the following example, the !UpdateUsingKeyOnly procedure is used for UPDATEs and DELETEs for all tables, including james.table1, although the user-defined procedures listed for james.table1 take precedence.

How to change the conflict resolution file while replication is active

You can change the conflict resolution file any time during replication to add and remove tables and procedures. After you change the conflict resolution file, stop and re-start the Post process.

Log information about resolved conflicts for Oracle database

You can configure the Post process to log information about successful conflict resolution operations if you are using the SharePlex prepared routines. This feature is disabled by default. For more information, see Log information about resolved conflicts for Oracle database.