In oracle apex, when I modify the data in interactive grid which has a union query, it gives me ORA-02014 error. How to handle this error?

Question

When I try to modify the interactive grid column it gives ORA-02014 error.

In back-end i have used sql query with union in it and also used join to join 3 tables.

I tried in the last couple of days and I expect someone might have the solution.

Answer 1

There might be other options than the one I'm going to suggest - such as

• rewriting query so that you don't join tables, but fetch lookup values via functions and then set those columns to read only so that updates don't affect them, or

• you could (in Apex) create your own process, such as this one (see "Help" for more sample code), with these settings:

  

---

I prefer creating a view and instead-of trigger which handles updates.

Here's an example; see if it helps.

View that joins some tables and contains union (that's what you described):

SQL> create or replace view v_emp_dept as
  2  select distinct d.deptno, d.dname, e.empno, e.ename, e.job
  3     from emp e join dept d on e.deptno = d.deptno
  4     where e.deptno = 10
  5  union all
  6  select distinct d.deptno, d.dname, e.empno, e.ename, e.job
  7     from emp e join dept d on e.deptno = d.deptno
  8     where e.deptno = 20;

View created.

This is one row:

SQL> select * from v_emp_dept where empno = 7369;

    DEPTNO DNAME               EMPNO ENAME      JOB
---------- -------------- ---------- ---------- ---------
        20 RESEARCH             7369 SMITH      CLERK

What happens if we try to update it? Fails (not with your error, though, but - it fails):

SQL> update v_emp_dept set deptno = 10 where empno = 7369;
update v_emp_dept set deptno = 10 where empno = 7369
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

Instead-of trigger:

SQL> create or replace trigger trg_ed
  2    instead of update
  3    on v_emp_dept
  4  begin
  5    update emp e set
  6      e.deptno = :new.deptno
  7      where e.empno = :new.empno;
  8  end;
  9  /

Trigger created.

Re-run the same update statement:

SQL> update v_emp_dept set deptno = 10 where empno = 7369;

1 row updated.

Right; now it works and updates value - not in a view (it just reflects that change), but in underlying table:

SQL> select * from v_emp_dept where empno = 7369;

    DEPTNO DNAME               EMPNO ENAME      JOB
---------- -------------- ---------- ---------- ---------
        10 ACCOUNTING           7369 SMITH      CLERK

SQL> select deptno from emp where empno = 7369;

    DEPTNO
----------
        10

SQL>

Answer 2

There error pretty much says it all. The database engine cannot determine how to update the data from the select query. When the interactive grid is rendered, it will try to do a SELECT * FROM (<the IG query>) FOR UPDATE . In your case, this statement is erroring out when you add the union. This is what happening under the hood:

query without union works fine:

select * from (
select * from emp where ename = 'KING'
) for update;

query with union works cannot be selected for update:

select * from (
select * from emp where ename = 'KING'
union all
select * from emp where ename = 'BLAKE'
) for update;
    
Error at line 1/15: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

Any of the solutions @Littlefoot mentions will could work.