How to Prevent Duplicate Row Interactive Gird Oracle Apex

April 23, 2020Posted inAPEX, IG, Interactive Grid

Sometimes user wants to prevent duplicate row on that column where columns don’t have a unique Id or Primary key Identification. On those cases here, I have provided a solution which will definitely help you.

  • Create an editable Interactive Grid using the following query:
Copy Code
select ROWID,
       EMPNO,
       ENAME,
       JOB,
        DEPTNO     
  from EMP;

  • Create validation on the column on which you want to prevent duplicate rows. Select validation type (PL/SQL Function returning Error text), copy and paste bellow PLSQL code:
Copy Code
DECLARE
   l_count   NUMBER;
BEGIN
   IF :apex$row_status = 'C'
   THEN                                              -- You can use 'C' OR 'I'
      SELECT COUNT (1)
        INTO l_count
        FROM emp
       WHERE empno = :empno;
   END IF;

   IF l_count >= 1
   THEN
      RETURN 'Employee Code ' || :empno || ' is duplicate';
   END IF;
END;

Demo

You might Like

Tags: