1. Overview
This document talks about finding duplicate values in the Interactive Grid using PLSQL.
2. Technologies and Tools Used
The following technologies have been used to achieve this functionality,
- PLSQL
- JavaScript
3. Use Case
In my last post, I discussed finding duplicates values in the IG using JavaScript and to the continuation, we will see how to validate from PLSQL calling from Ajax request.
4. Architecture
Assume that there is an editable Interactive grid for the table EMP and the scenario is to validate the column ENAME to check it contains the duplicate value or not.
Let us see step by step,
Step 1: Create an Interactive Grid for the table EMP.
Step 2: Then, go to the column ENAME and navigate to the Advanced section and provide the static id as ENAME.
Step 3: Then, create a dynamic action for the ENAME column as below,
e.g.
Name – On change of ENAME
Event – Change
Selection Type – Column(s)
Region – EMP Interactive Grid <Your Interactive Region>
Column(s) – ENAME
Action – Execute JavaScript Code
Code –
apex.server.process(
“CHK_ENAME_EXST”, { //Ajax Callback process Name
x01: apex.item(‘ENAME’).getValue()
}, {
dataType: ‘text’,
success: function(pData) {
if (pData = ‘Y’) {
alert(‘Duplicate Found’);
}
}
}
);
Step 4: Next, create a process named CHK_ENAME_EXST. Then go to the Execution Option section of the process and change the Point attribute to Ajax Callback.
Step 5: Now place the below code in the PL/SQL Code attribute under the Source section of the process,
e.g.
DECLARE
CURSOR chk_ename (p_cur_ename IN emp.ename%TYPE)
IS
SELECT 1
FROM emp
WHERE ename = p_cur_ename;
lv_cnt NUMBER;
BEGIN
OPEN chk_ename (p_cur_ename => APEX_APPLICATION.g_x01);
FETCH chk_ename INTO lv_cnt;
CLOSE chk_ename;
IF lv_cnt = 1
THEN
HTP.prn (‘Y’);
ELSE
HTP.prn (‘N’);
END IF;
END;
Step 6: Finally, save the changes and run the page.
Note: Using the above steps helps to validate the duplicate rows by checking the row exists in the table or not. To validate the duplicates between the newly added rows, please check the following link, Validate IG duplicate rows via JS
