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:

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.

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.