References: Oracle Office Hours
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;
- Add Form page
- Select SQL Query as Data source
- Select Primary key Column
- 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;
