...
See also Data available to apply APPLY sessions for further information available to the handler.
| Note |
|---|
It is not possible to pass modified values back to applyAPPLY. If you are not satisfied with the return value options (discard, retry, newer, older), you have to apply 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.
| No Format | ||
|---|---|---|
| ||
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.
| No Format | ||
|---|---|---|
| ||
grant execute on scott.emp_update_conflict_nodata to dbvrep; |
3. Set the PL/SQL conflict handler.
| No Format | ||
|---|---|---|
| ||
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.
| No Format | ||
|---|---|---|
| ||
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; |
| Note |
|---|
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:
| No Format | ||
|---|---|---|
| ||
dbvrep> set_conflict_handlers for table scott.conflicts_plsql_add for update on data to PLSQL scott.testsuite6_plsql_add |