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.