Build Tabular Forms for Multi-Row Operations
Based on Oracle Application Express (formerly HTML DB] version 1.5
After completing this How-To, you should be able to understand how to:
Table of Contents
In Oracle HTML DB, you can create a tabular form in two ways:
The easiest way to build a tabular form is to use Tabular Form wizard. This wizard creates a tabular form with a built in multiple row update process. This built in process does optimistic locking behind the scene to maintain the data integrity. In certain cases you may want to build a tabular form manually to control the display and functionality. You can use HTMLDB_ITEM package to build a tabular form manually. When you build a tabular form using HTMLDB_ITEM, you will need to build your own optimistic locking since it is not done automatically during update. This How To describes how you can create a tabular form using EMP table that is included when a new workspace is provisioned.
The simplest way to build a tabular form is to use Tabular Form wizard. This wizard creates a tabular form with a built in multiple row update process. This process checks for MD5 checksum values before doing the update in order to perform optimistic locking behind the scene. When you build a tabular form using the wizard, there are some editing restrictions. We will go over these restrictions in the later steps.
We will start by creating a tabular form on EMP table using the wizard with following steps:
When the wizard creates the tabular form, all updateable columns display as text field by default. The following section describes how to change this default display to a select list.
To change the default display of JOB and DEPTNO to a select list:
Run the page. The tabular form displays with JOB and DEPTNO column in select list (see Figure 3).
Figure 3 - Wizard created tabular form
If you create a tabular form using the wizard, the wizard creates a SQL Query (updateable report) region type. When you wish to change the display type of updateable columns for this type of region, you need to use updateable column attributes. If you change the display type using HTMLDB_ITEM package in the select list of a SQL statement, the update will not work properly. For example, the wizard generated SQL statement cannot be mixed with HTMLDB_ITEM package:
select htmldb_item.hidden(1,empno) empno, "ENAME", "JOB", htmldb_item.select_list_from_query(2,mgr,'select ename, empno from emp') mgr, "HIREDATE", "SAL", "COMM", "DEPTNO" from "#OWNER#"."EMP" order by 1The select list of a SQL statement of a tabular form should not be modified after it has been generated. Doing so can result in a checksum error when altering data of the form and applying updates. Consider the following example:
select ename from emp;Note that this should not be altered to:
select lower(ename) from emp.
You can also create a tabular form manually by defining SQL report query using HTMLDB_ITEM package. When you build a tabular form manually, you have more control on editing the tabular form query and processing the update. However, be aware this manual approach does involve more steps. If you wish to build a complex tabular form, it is best to take the manual approach. Otherwise, building a simple tabular form can be easily done using the wizard.
To build a tabular form manually:
To enhance the display of the report, you can change the report column headings as follows:
To submit the tabular form array values, you need to add a button.
To add a button, follow these steps:
To update employee records, you need to add a process:
When you run the page, you should see a tabular form looks like Figure 5.
Figure 5 - Manually created tabular form
Building a tabular form with an extra row for insert is useful when you wish to have a tabular form that does both insert and update. This section describes how to add an extra insert row to the tabular form.
To add an extra insert row to a tablular form:
The primary key column, EMPNO, is not an updateable column on the tabular form. Since the primary key is required to insert the data into the EMP table, you need to have a trigger populate the primary key using a sequence. You can create a sequence and trigger in SQL Workshop.
To create a sequence and trigger using SQL Workshop:
Figure 7 - Tabular form with extra row for insert
Since you created this tabular form manually, optimistic locking is not done automatically behind the scenes. You need to add an optimistic locking to the tabular form in order to keep the data integrity. You can use HTMLDB_ITEM.MD5_CHECKSUM to include MD5 checksum values on the form. When the page is submitted, HTMLDB_ITEM.MD5_CHECKSUM populates the global HTMLDB_APPLICATION.G_FCS with a checksum of the values provided.
To add the checksum values, you need to edit the tabular form query as follows:
To assure the data integrity, next you need to edit the update process to get the original checksum values and compare them with the submitted checksum values. If they are not same, the application will raise an error.
To implement this, change the update process as follows:
ORA-20001: Current version of data in database has changed since user initiated update process.
Collections enable you to temporarily capture one or more non-scalar values. You can use collections to store rows and columns currently in session state so they can be accessed, manipulated, or processed during a user's specific session. Think of a collection as a bucket in which you can temporarily store and name rows of information. This section describes how to add validations to a tabular form. The tabular form does not keep session state of the user input when the validations fail. To save the user input, you can use collections.
To start, you first create a not null validation to validate the user input.
To create a not null validation to validate the user input:
The tabular form does not keep the session state when the validation fails. You can use collections to save the user input by creating a process as follows:
In order to show session state of the tabular form, you need to display the tabular form the previously saved collection. To make this happen, we will create a new tabular form region as follows:
To submit the values from tabular form you created previously, you need to add a button.
To add a button, follow these steps:
Since you added a new region that displays tabular form from a collection, you need to change the update process to evaulate the checksum values from the new tabular form as well.
To change the update process, follow these steps:
Now the existing tabular form region from EMP table needs to display only when there is no existing collection. To accomplish this, you need to add a conditional display as follows:
Next, you will create a before header process to delete the collection. This process will ensure the application will not display the tabular form from a collection when the page is rendered initially.
To create a before header process:
Now try to update and insert employee records. It will validate user input and do optimistic locking before doing the update and insert (see Figure 9).
Figure 9 - Tabular form with validation
Discuss this how-to in the OTN HTML DB Forum.