Oracle Distributed Systems

Types of Conflicts Detected

Oracle detects conflicts based on PL/SQL exceptions, as summarized in Table 15.1, only at the destination site. Note that conflict detection does not imply conflict resolution.

Table 15-1. Detectable PL/SQL Exceptions

Type of DML	Potential Conflicts
INSERT	DUP_VAL_ON_INDEX
UPDATE	SQL%ROWCOUNT = 0 (NO_DATA_FOUND) SQL%ROWCOUNT > 1 (TOO_MANY_ROWS) DUP_VAL_ON_INDEX
DELETE	SQL%ROWCOUNT = 0 (NO_DATA_FOUND) SQL%ROWCOUNT > 1 (TOO_MANY_ROWS)

The situations for which Oracle does not resolve conflicts include:

• Deletes that raise NO_DATA_FOUND errors (even though they are detected)

• Deletes that raise TOO_MANY_ROWS errors

• Use of NULL values in columns used for conflict resolution

• DML that violates referential integrity constraints

• Conflicts arising from procedural replication

Why not? A brief analysis of Oracle’s implementation reveals why these restrictions must exist.

Limitations of Delete Conflict Resolution

Because of the difficulties of processing delete conflicts, Oracle’s recommendation is to design replicated applications to flag records as deleted. Include a STATUS column in the table and update it to D, for example, instead of actually deleting the row. This way, you can avoid all potential delete conflicts and avoid the task of writing your own delete conflict handling procedure. You can perform the actual delete at scheduled intervals using procedural replication. If this is not an option for your application, then consider the following alternatives.

If a row deleted ...