Forms Reloaded APEX 19.2 Part 2

APEX Form region On Query | Part 2

Let's try some insights of APEX form region.

Below script to add Emp and Dept if you don't have.

CREATE TABLE dept
(
   deptno   NUMBER (2) CONSTRAINT pk_dept PRIMARY KEY,
   dname    VARCHAR2 (14),
   loc      VARCHAR2 (13)
);

CREATE TABLE emp
(
   empno      NUMBER (4) CONSTRAINT pk_emp PRIMARY KEY,
   ename      VARCHAR2 (10),
   job        VARCHAR2 (9),
   mgr        NUMBER (4),
   hiredate   DATE,
   sal        NUMBER (7, 2),
   comm       NUMBER (7, 2),
   deptno     NUMBER (2) CONSTRAINT fk_deptno REFERENCES dept
);

INSERT INTO dept
     VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO dept
     VALUES (20, 'RESEARCH', 'DALLAS');

INSERT INTO dept
     VALUES (30, 'SALES', 'CHICAGO');

INSERT INTO dept
     VALUES (40, 'OPERATIONS', 'BOSTON');

INSERT INTO emp
     VALUES (7369,
             'SMITH',
             'CLERK',
             7902,
             TO_DATE ('17-12-1980', 'dd-mm-yyyy'),
             800,
             NULL,
             20);

INSERT INTO emp
     VALUES (7499,
             'ALLEN',
             'SALESMAN',
             7698,
             TO_DATE ('20-2-1981', 'dd-mm-yyyy'),
             1600,
             300,
             30);

INSERT INTO emp
     VALUES (7521,
             'WARD',
             'SALESMAN',
             7698,
             TO_DATE ('22-2-1981', 'dd-mm-yyyy'),
             1250,
             500,
             30);

INSERT INTO emp
     VALUES (7566,
             'JONES',
             'MANAGER',
             7839,
             TO_DATE ('2-4-1981', 'dd-mm-yyyy'),
             2975,
             NULL,
             20);

INSERT INTO emp
     VALUES (7654,
             'MARTIN',
             'SALESMAN',
             7698,
             TO_DATE ('28-9-1981', 'dd-mm-yyyy'),
             1250,
             1400,
             30);

INSERT INTO emp
     VALUES (7698,
             'BLAKE',
             'MANAGER',
             7839,
             TO_DATE ('1-5-1981', 'dd-mm-yyyy'),
             2850,
             NULL,
             30);

INSERT INTO emp
     VALUES (7782,
             'CLARK',
             'MANAGER',
             7839,
             TO_DATE ('9-6-1981', 'dd-mm-yyyy'),
             2450,
             NULL,
             10);

INSERT INTO emp
     VALUES (7788,
             'SCOTT',
             'ANALYST',
             7566,
             TO_DATE ('13-JUL-87', 'dd-mm-rr') - 85,
             3000,
             NULL,
             20);

INSERT INTO emp
     VALUES (7839,
             'KING',
             'PRESIDENT',
             NULL,
             TO_DATE ('17-11-1981', 'dd-mm-yyyy'),
             5000,
             NULL,
             10);

INSERT INTO emp
     VALUES (7844,
             'TURNER',
             'SALESMAN',
             7698,
             TO_DATE ('8-9-1981', 'dd-mm-yyyy'),
             1500,
             0,
             30);

INSERT INTO emp
     VALUES (7876,
             'ADAMS',
             'CLERK',
             7788,
             TO_DATE ('13-JUL-87', 'dd-mm-rr') - 51,
             1100,
             NULL,
             20);

INSERT INTO emp
     VALUES (7900,
             'JAMES',
             'CLERK',
             7698,
             TO_DATE ('3-12-1981', 'dd-mm-yyyy'),
             950,
             NULL,
             30);

INSERT INTO emp
     VALUES (7902,
             'FORD',
             'ANALYST',
             7566,
             TO_DATE ('3-12-1981', 'dd-mm-yyyy'),
             3000,
             NULL,
             20);

INSERT INTO emp
     VALUES (7934,
             'MILLER',
             'CLERK',
             7782,
             TO_DATE ('23-1-1982', 'dd-mm-yyyy'),
             1300,
             NULL,
             10);

COMMIT;

We are creating a form on SQL Query as that's the first familiar after form on table. 

• Simple Query

Select * from emp WHERE deptno= 20;

• Complex Query

SELECT e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.hiredate,
       e.sal,
       e.comm,
       e.deptno
  FROM emp e
  inner join dept d
  on e.deptno= d.deptno
 WHERE d.deptno = 20;

1. Add Form page 
2. Select SQL Query as Data source
3. Select Primary key Column
4. Save and Run

Now when we are creating forms for Simple queries there is no problem in updating. inserting or deleting rows. But when it comes to complex queries there is slight issue. Let's check them out.

Try to update the any department name

So, we are having issue here. When update department table it gives the error "ORA-01776: cannot modify more than one base table through a join view".

Now let's try same with emp table column. As per the above error we except the same error.

Now the question is why are we getting this error?

Let's try to update the same scenario at database level.

UPDATE (SELECT e.empno,
               e.ename,
               e.job,
               e.mgr,
               e.hiredate,
               e.sal,
               e.comm,
               d.dname,
               e.deptno
          FROM emp e
          INNER JOIN dept d
           ON e.deptno = d.deptno
         WHERE d.deptno = 20 AND empno = 7902)
   SET dname = 'CLERK 2', comm = 700;

Boom, the issue is at database level. Oracle doesn't allow to update two tables at a time in above cases. 

To Resolve it we have two approaches.

• Avoid updating any of one table columns
• Creating custom PL/SQL code for DML

Avoid updating any of one table columns

Make few changes in dept table columns.

• Source: 
• Query Only: Yes
• Read Only : Always

Now it works. Great.

Creating custom PL/SQL code for DML

• DML Process
• Settings
• Target Type: PL/SQL Code
• PL/SQL Code to Insert/Update/Delete: Source code below

BEGIN
   CASE :apex$row_status
      WHEN 'C'
      THEN
         INSERT INTO dept (deptno, dname)
              VALUES ( :p3_deptno, :p3_dname);
         INSERT INTO emp (empno,
                          ename,
                          deptno,
                          comm)
              VALUES ( :p3_empno,
                      :p3_ename,
                      :p3_deptno,
                      :p3_comm);
      WHEN 'U'
      THEN
         UPDATE emp
            SET ename = :p3_ename,
                deptno = :p3_deptno,
                comm = :p3_comm
          WHERE empno = :p3_empno;
         UPDATE dept
            SET dname = :p3_dname
          WHERE deptno = :p3_deptno;
      WHEN 'D'
      THEN
         DELETE emp
          WHERE empno = :p3_empno;
         DELETE dept
          WHERE deptno = :p3_deptno;
   END CASE;
END;