...
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| SET_CONFLICT_HANDLERS FOR TABLE scott.avi FOR UPDATE ON NO_DATA DATA TO PLSQL scott.dbvisit.nodata_update_avi |
|
|
...
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| function_name(apply_old_data table%rowtype, has_found_apply_row boolean, primary_key_data table%rowtype, new_data table%rowtype) return number; |
|
|
...
| 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
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 |
|---|
| 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 |
|---|
| grant execute on scott.emp_update_conflict_nodata to dbvrep; |
|
|
3. Set the PL/SQL conflict handler.
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| SET_CONFLICT_HANDLERS FOR TABLE scott.emp FOR UPDATE ON NO_DATA DATA TO PLSQL scott.emp_update_conflict_nodata |
|
|