In Oracle form's multi record block sometimes it is required that user should not enter duplicate values in an item and a alert should warn user that he is trying to add duplicate values for that item before saving the record.
Let us take the following example of the multi record PHONE block in oracle form. PHONE block has 2 items phone_type (select type) and phone_number it is required. User should not be allowed to select the same values in the select list. See the following figure.
Create following triggers in form
When-Validate-item – Create this trigger on that item against which you want to introduced duplicate check. In this case it would be Phone Type as we don’t want user to add same type more than once.
When-new-item-instance – There is an restriction on when-validate-item trigger that we cannot use navigation command on this i.e. go_item, go block, first_record, last_record etc. so we need to create when new item instance trigger ( form level or block level) and use go_item.
• Create control block (LOCAL) and a item CHECK_DUPLICATE
• Create an Alert called ALERT_STOP.
Write the following code on When-Validate-Item Trigger on Phone type
/* Setting flag value to Y */
:LOCAL.CHECK_DUPLICATE := 'Y';
Write following code on When-New-Item-Instance Trigger on form level or block level
V_CURRENT_RECORD NUMBER := 0;
V_LAST_RECORD NUMBER := 0;
V_ALERT_NO NUMBER := 0;
IF NVL(:LOCAL.CHECK_DUPLICATE,'N') = 'Y' THEN
/* Setting Flag to N so that it would not get fire again until validated once
:LOCAL.CHECK_DUPLICATE := 'N';
V_CURRENT_RECORD := TO_NUMBER(GET_BLOCK_PROPERTY('PHONES',CURRENT_RECORD));
V_DATA := :PHONES.PHO_TYPE;
V_LAST_RECORD := TO_NUMBER(GET_BLOCK_PROPERTY('PHONES',CURRENT_RECORD));
FOR V_INDEX IN 1..V_LAST_RECORD
IF V_INDEX != V_CURRENT_RECORD AND
V_DATA = :PHONES.PHO_TYPE THEN
'Duplicate Telephone Type.');
V_ALERT_NO := show_alert('ALERT_STOP');
Mantra IT Technical Team - Share and spread knowledge.
All information and materials provided here are provided "as-is" by Mantra IT technical team based on their vast knowledge and experience. Mantra IT shall not be liable for any damages, including direct, indirect, incidental, special or consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or these materials. You may reach Mantra IT technical team by emailing on email@example.com for any further assistance.