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 PostgreSQL Database as a Service to Oracle.

Important! This documentation provides guidelines, examples and templates to assist you, but do not use them as your own routines. Test your conflict resolution routines before you put them into production to make sure they work as intended, and to make sure that one routine does not counteract another one. By default, SharePlex does not stop for out-of-sync conditions. If failed attempts at conflict resolution are not resolved, the databases can become more and more out of synchronization. Check the Event Log frequently to monitor for out-of-sync warnings by using the show log command in sp_ctrl. See the SharePlex Reference Guide for more information about show log and other SharePlex commands. Updates are occasionally made to the conflict resolution logic, so refer to the Release Notes and documentation for your version of SharePlex for any additional information that augments or supersedes these instructions.

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.

Before you get started, understand that any table to be accessed through PL/SQL for conflict resolution requires implicitly granted privileges from the owner of the object to SharePlex.

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

For the procedure interface information of PostgreSQL, see Procedure interface for PostgreSQL.

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.oraA, where oraA is a sid of OracleDB, 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.oraA, where oraA is a sid of OracleDB.

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.

SchemaName.tableName

IUD

schema.procedure

where:

• IUD is the type of operation that creates the conflict that is resolved with the specified procedure.
• schema.procedure is the schema 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.
• 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.

Example conflict resolution file

scott.sal	IUD	scott.sal_cr
scott.cust	IUD	scott.cust_cr
!DEFAULT	IUD	scott.sal_cr5

How it works:

• The scott.sal_cr routine is used for the scott.sal table before the scott.sal_cr5 procedure is used for that table.
• For scott.cust, a procedure scott.cust_cr is called for IUD before the default 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 schema 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.

!DEFAULT	IUD	proc0
schema.table1	IUD	proc1
james.table1	IUD	proc2
james.table1	IUD	proc3

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.