Custom pl/sql code to process the IG data on submit instead of the native process

Question

How can I use custom pl/sql code to process insert update the IG data (which is attached with inner join) on submit instead of the native process in oracle apex Interactive Grid. If anyone have solution please reply.

SELECT dept.DNAME,
       emp.empno,
       emp.ename,
       emp.job,
       emp.mgr,
       emp.hiredate 
  FROM emp
 INNER JOIN dept ON
   emp.deptno = dept.deptno

user_4189814 · Answer

To update just the emp record (it should be trivial to add code for the dept table) you could use this block.

begin
    if :APEX$ROW_STATUS = 'D' then
        -- DELETE the record
        delete from emp
         where empno = :EMPNO;
         
    elsif :APEX$ROW_STATUS = 'U' then
        update emp SET 
               ename = :ENAME,
               job = :JOB,
               mgr = :MGR,
               hiredate = :HIREDATE
         where empno = :EMPNO;
    elsif :APEX$ROW_STATUS = 'C' then
         insert into emp (ename, job, mgr, hiredate) 
          values (:ENAME, :JOB, :MRG, :HIREDATE);
    end if;
end;

There are a couple of very complete blogs on this topic, for example here: https://mikesmithers.wordpress.com/2019/07/23/customizing-dml-in-an-apex-interactive-grid/, make sure to also check the references section at the bottom.

Custom pl/sql code to process the IG data on submit instead of the native process

1 Answers