Oracle Forms
Avoid duplicated records in a block
The purpose is to reject two records that contain duplicated values.
The technique used to solve this problem comes from the Kevin D Clarke’s
calculated item famous solution.
It uses two calculated items, one
in the data bock and another in a control block.
The first calculated item (:DEPT.MATCH_FOUND) is added to the DEPT
block. It contains the formula as follow:
Comparaison(:ctrl.charsave,
:dept.deptno||:dept.dname)
Notice in this case,that we want to avoid duplicates on both DEPTNO and
DNAME values.
Function COMPARAISON (val1
varchar2, val2 varchar2)
Return number
Is
answer
number := 0;
Begin
if val1 =
val2 then
answer :=
1;
end if;
return(answer);
End;
COMPARAISON is a program unit stored in the Forms module.
The two values are compared to each other, then the function returns 1
(a value greatest than 0) if both the values are identical.
The first value (:ctrl.charsave) contains the bakup value of the current
record.
The DEPT block must have the following properties setting:
|
Query all records |
YES |
The CTRL block must have the following properties setting:
|
Query all records |
YES |
|
Single record |
YES |
|
Database data block |
NO |
The second calculated item (:CTRL.MATCH_FOUND) is added to the CTRL
block.
It summarize the values contained in all the rows of the DEPT block
(dept.match_found).
If the total is greater than 1, we have two duplicated data.
The sample dialog
·
Download
the DUPLICATES.fmb sample dialog for you to test