PLSQL Conflict Handler

You can create a PL/SQL function to handle a particular conflict. This is useful when there are specific business rules that describe the behavior when there is a conflict. With PL/SQL different conflict handlers can be created that are not handled by the supplied conflict handlers. For example the ADD or DELTA conflict handler which adds up the different updated values from both source and target can be programmed using PL/SQL.

The following is an example of the command set 'nodata_update_avi' function to be executed when there is 'NO_DATA' conflict raised by Dbvisit Replicate for update operation on scott.avi table.

SET_CONFLICT_HANDLERS FOR TABLE scott.avi FOR UPDATE ON NO_DATA 
TO PLSQL scott.dbvisit.nodata_update_avi

The function must have following prototype:

function_name(apply_old_data table%rowtype, has_found_apply_row boolean, 
primary_key_data table%rowtype, new_data table%rowtype) return number;

The following applies:

apply_old_data : the values at the target, queried by using key columns in the where clause. The boolean flag indicates whether any such record was found.

primary_key_data: old values at the source; mostly used to see the key, but it actually contains all supplementally logged values, too. Thus it will always (excl. LOBs, LONGs) include the modified column, too.

new_data: new (modified) values at the source.

The return values are:

0: Discard
1: Overwrite
2: Retry
3: The PL/SQL function resolved the conflict by itself and made all necessary changes.
4: Force

The function must not issue a commit, as the transaction may be yet rolled back, if a rollback happened on source.

See also Data available to APPLY sessions for further information available to the handler.

It is not possible to pass modified values back to APPLY. If you are not satisfied with the return value options (discard, retry, newer, older), you have to APPLY the change yourself.

Example 1

Following example shows the configuration of PL/SQL conflict handler for scott.emp table to handle NO_DATA conflicts for UPDATE statements.

1. Create a function to handle conflict. Following function first verifies existence of the record to be updated. If it exists, function will update it with new values and insert corrupted record in scott.empaudit table for further investigation otherwise it will simply inserts a new record in scott.emp table containing new values.

create function scott.emp_update_conflict_nodata
(apply_old_data scott.emp%rowtype,
 has_found_apply_row boolean,
 primary_key_data scott.emp%rowtype,
 new_data scott.emp%rowtype)


return number is
begin
	if has_found_apply_row then
 		update scott.emp set name=new_data.name,sal=new_data.sal,
		grade=new_data.grade,dept=new_data.dept
		where empno=new_data.empno;
 		insert into scott.emp_audit values apply_old_data;
	else
		insert into scott.emp values new_data;
 	end if;
 
	return 3;
end;
/

2. Grant execute privilege on this function to dbvrep user.

grant execute on scott.emp_update_conflict_nodata to dbvrep;

3. Set the PL/SQL conflict handler.

SET_CONFLICT_HANDLERS FOR TABLE scott.emp FOR UPDATE ON NO_DATA 
TO PLSQL scott.emp_update_conflict_nodata

Example 2

The following is a simple example of the ADD (DELTA) conflict handler.

create function scott.testsuite6_plsql_add(apply_old_data      conflicts_plsql_add%rowtype,
                                           has_found_apply_row boolean,
                                           primary_key_data    conflicts_plsql_add%rowtype,
                                           new_data            conflicts_plsql_add%rowtype)
  return number is
begin
  update scott.conflicts_plsql_add
     set value_column = value_column + (new_data.value_column - primary_key_data.value_column)
   where key_column = apply_old_data.key_column;
  return 3;
end;
/

grant execute on scott.testsuite6_plsql_add to dbvrep;

Please note that this is a very simple example and lacks any error or value checking and only handles one type of conflict.

To set the conflict handler, use the following command:

dbvrep> set_conflict_handlers for table scott.conflicts_plsql_add for update on data to PLSQL scott.testsuite6_plsql_add