...
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.
| Section |
|---|
| Column |
|---|
|
| Panel |
|---|
| No Format |
|---|
|
SET_CONFLICT_HANDLERS FOR TABLE scott.avi FOR UPDATE ON NO_ DATA DATA
TO PLSQL scott.dbvisit.nodata_update_avi |
The function must have following prototype:
| Section |
|---|
| Column |
|---|
|
| Panel |
|---|
| No Format |
|---|
|
function_name(apply_old_ data table%rowtypedata table%rowtype, has_found_apply_row boolean,
primary_key_ data table%rowtypedata table%rowtype, new_ data table%rowtypedata table%rowtype) return number; |
| Info |
|---|
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. |
...
| Note |
|---|
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.
| Section |
|---|
| Column |
|---|
|
| Panel |
|---|
| 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.
| Section |
|---|
| Column |
|---|
|
| Panel |
|---|
| No Format |
|---|
|
grant execute on scott.emp_update_conflict_nodata to dbvrep; |
3. Set the PL/SQL conflict handler.
| Section |
|---|
| Column |
|---|
|
panel| No Format |
|---|
|
SET_CONFLICT_HANDLERS FOR TABLE scott.emp FOR UPDATE ON NO_ DATA DATA
TO PLSQL scott.emp_update_conflict_nodata |
Example 2
The following is a simple example of the ADD (DELTA) conflict handler.
| Section |
|---|
| Column |
|---|
|
| Panel |
|---|
| 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:
| Section |
|---|
| Column |
|---|
|
| Panel |
|---|
| No Format |
|---|
|
dbvrep> set_conflict_handlers for table scott.conflicts_plsql_add for update on data to PLSQL scott.testsuite6_plsql_add |