A tabular form enables users to update multiple rows in a table at once using a single page. You can use the Tabular Form Wizard to create a tabular form that contains a built-in multiple row update process. This built-in process performs optimistic locking behind the scenes to maintain the data integrity.
This tutorial explains how to create a tabular form within an existing application and then how to change one of the updatable columns from a text field to a select list.
This section contains the following topics:
Oracle HTML DB installs with a number of demonstration applications. In this exercise you will create a tabular form within the demonstration application, Sample Application.
To see if Sample Application is installed:
Log in to Oracle HTML DB.
From the Workspace Administration list, select Review Demonstration Applications.
Locate Sample Application and check the Status column:
If the Status column displays Installed, return to the Workspace home page.
If the Status column displays Not Installed, select Install in the Action column.
Follow the on-screen instructions.
The Tabular Form Wizard creates a form to perform update, insert, and delete operations on multiple rows in a database table. Additionally, the wizard creates a multiple row update process that checks for MD5 checksum values before doing the update in order to prevent lost updates. The following exercise creates a tabular form on the EMP
table.
To create a tabular form using the Tabular Form Wizard:
Navigate to the Workspace home page.
From the Applications list, select Sample Application.
Click Create Page.
On Create New Page, select Page with Component and click Next.
On Select Component Type, select Form and click Next.
On Create Page, select Tabular Form and click Next.
On Identify Table/View Owner:
From Table/View Owner, select the owner of the EMP
table.
From Allowed Operations, accept the default.
Click Next.
For Table/View Name, select EMP and click Next.
On Identify Columns to Display:
For Use User Interface Defaults, accept the default.
For Select Columns, select the following columns:
ENAME, JOB, HIREDATE, SAL, COMM
Note: This exercise limits the number of columns to optimize the display on-screen. For a real form, you would probably want to include additional columns. |
Click Next.
For Primary Key Column 1, accept the default EMPNO and click Next.
For Source Type, accept the default Existing trigger and click Next.
For Updatable Columns, select the following columns and click Next:
JOB, HIREDATE, SAL, COMM
On Identify Page and Region Attributes:
For Page, enter 800
.
For Page Name, enter Tabular Form on EMP
.
For Region Name, enter Tabular Form on EMP
.
Click Next.
On Identify Tab, accept the default, Do not use tabs, and click Next.
On Button Labels:
Accepts the defaults for the Cancel, Delete, and Add Row buttons.
For the Submit button, enter Apply Changes
.
Click Next.
Accept the remaining defaults, click Next, and then click Finish.
Next, run the page to view your new form.
To run the page:
Click Run Page.
If prompted to enter a username and password:
For User Name, enter either demo
or admin
.
For Password, enter the name of the current workspace using all lowercase letters.
Click Login.
Navigate to page 800:
Select Edit Page from the Developer Toolbar.
In Page, enter 800 and click Go.
Click the Run Page icon.
As shown in Figure 1-1, note the tabular form contains four buttons. Cancel, Delete, and Apply Changes display in the upper right corner and Add Row displays at the bottom. Additionally, a check box appears to the left of each row enabling the user to select one row at a time, or a user can select all rows at once by selecting the check box to the left of the column headings. The check box is used in conjunction with the Delete button to identify the rows to be deleted.
When the Tabular Form Wizard creates a tabular form, updatable columns display by default as text field. You can change this default display by editing report column attributes.
To change the default display of JOB
to a select list:
Navigate to the Page Definition for page 800. Select Edit Page from the Developer Toolbar.
Under Regions, click Q next to Tabular Form.
The Region Attributes page appears.
Under Report Column Attributes, click the Edit icon next to the JOB column.
Scroll down to Tabular Form Element. From Display As, select Select List (query based LOV).
Under List of Values, enter the following LOV query:
SELECT DISTINCT job a, job b FROM emp
Click Apply Changes.
Click the Run Page icon in the upper right corner the page.
As shown in Figure 1-2, notice the Job column now displays as a select list.
Note: Do not modify the select list of a SQL statement of a tabular form 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 |