Custom Code for Tabular Forms (Part 2)

The previous article covered how to create a Tabular Form with manual code rather than automatic row processing. This article will demonstrate how to change the Tabular Form to modify data from multiple tables using the same Tabular Form.

Modify Tabular Form

Using the example from the previous article, modify the Tabular Form and change the query to:

1
2
3
4
5
6
7
8
9
select
e.empno,
e.empno empno_display,
e.ename,
e.sal,
d.dname
from emp e, dept d
where 1=1
and e.deptno = d.deptno

Edit DNAME

Edit the newly added DNAME column with the following changes:


Modify Page process

Change the page process to use the code snippet below. Note: This isn’t the best example, as the below code will update the department name for each modified employee record. It does highlight is that you can reference and modify data from multiple tables.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
if :empno is null then
-- code to insert emp
null;
else
update emp
set
ename = :ename,
sal = :sal
where empno = :empno;

-- Update dept name
update dept d
set dname = :dname
where 1=1
and d.deptno = (
select e.deptno
from emp e
where e.empno = :empno)
end if;

If you’ve ever developed a true manual tabular form using collections, the above approach covered in this article may be a better alternative to manage and maintain.