Skip Headers
Oracle® Application Express Advanced Tutorials
Release 3.2

Part Number E11945-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

2 How to Create a Tabular Form

A tabular form enables users to update multiple rows in a table at once from 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 a new application and then how to change one of the updatable columns from a text field to a select list. Before you begin, you need to import and install the OEHR Sample Objects application in order to access the necessary sample database objects. See "About Loading Sample Objects".

This section contains the following topics:

For additional examples on this and related topics, please visit the following Oracle by Examples (OBEs):

Creating an Application

First, you need to create an application using the Create Application Wizard.

To create an application using the Create Application Wizard:

  1. On the Workspace home page, click the Application Builder icon.

    The Application Builder home page appears.

  2. Click Create.

  3. Select Create Application and click Next.

  4. For Name:

    1. Name - Enter Tabular Form.

    2. Application - Accept the default.

    3. Create Application - Select From scratch.

    4. Schema - Select the schema where you installed the OEHR sample objects.

    5. Click Next.

      Next, you need to add a page. You have the option of adding a blank page, a report, a form, a tabular form, or a report and form. For this exercise, you create an application containing a blank page. Then, you create a tabular form.

  5. Add a blank page:

    1. Under Select Page Type, select Blank and click Add Page.

      The new page appears in the list at the top of the page.

    2. Click Next.

  6. For Tabs, accept the default, One Level of Tabs, and click Next.

  7. For Copy Shared Components from Another Application, accept the default, No, and click Next.

  8. For Attributes, accept the defaults for Authentication Scheme, Language, and User Language Preference Derived From and click Next.

  9. For User Interface, select Theme 2 and click Next.

    A theme is collection of templates that define the layout and style of an application. You can change a theme at any time.

  10. Review your selections and click Create.

    The Application home page appears.

See Also:

"Managing Themes" in Oracle Database Application Express User's Guide

Creating a Tabular Form Using a Wizard

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 to prevent lost updates. In the following exercise you create a tabular form on the OEHR_EMPLOYEES table.

To create a tabular form using the Tabular Form Wizard:

  1. On the Application home page, click Create Page.

  2. For the page type, select Form and click Next.

  3. Select Tabular Form and click Next.

  4. For Table/View Owner:

    1. Table/View Owner - Accept the default.

    2. Allowed Operations - Accept the default, Update, Insert, and Delete.

    3. Click Next.

  5. For Table/View Name, select OEHR_EMPLOYEES and click Next.

  6. For Displayed Columns:

    1. For Select Columns, press Ctrl and select the following columns:

      FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY, DEPARTMENT_ID
      

      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.
    2. Click Next.

  7. For Primary Key, accept the default, EMPLOYEE_ID (Number) and click Next.

  8. For Source Type, accept the default, Existing trigger, and click Next.

  9. For Updatable Columns, select all columns and click Next.

  10. For Page and Region Attributes:

    1. Page - Accept the default.

    2. Page Name - Enter Tabular Form.

    3. Region Title - Accept the default, Tabular Form.

    4. Region Template and Report Template - Accept the defaults.

    5. Breadcrumb - Accept the default.

    6. Click Next.

  11. For Tab, accept the default, Do not use tabs, and click Next.

  12. For Button Labels, specify the following:

    1. Submit button - Enter Apply Changes.

    2. Cancel, Delete, and Add Row buttons - Accept the default label text.

    3. Click Next.

  13. For Branching, accept the defaults and click Next.

    Branching tells the Web browser what page to display when the current page is submitted for processing. In this case, you want the user to remain on the current page.

  14. Confirm your selections and click Finish.

Next, run the page to view your new form.

To run the page:

  1. Click the Run Page icon as shown in Figure 2-1.

  2. If prompted to enter a user name and password, enter your workspace user name and password and click Login. See "About Application Authentication".

    The tabular form appears as shown in Figure 2-2.

As shown in Figure 2-2, note that the tabular form contains four buttons. Cancel, Delete, and Apply Changes display in the upper right corner and Add Row displays in the bottom right corner. Additionally, a check box appears to the left of each row to enable the user to select the current row. Users can also select all rows at once by selecting the check box to the left of the column headings. The same check box is also used in conjunction with the Delete button to identify the rows to be deleted.

Note that the overall form layout (that is, the color scheme, button placement, region header placement, and so on) are controlled by templates in the currently selected theme.

See Also:

"Managing Themes" in Oracle Database Application Express User's Guide

Changing an Updatable Column to a Select List

When the Tabular Form Wizard creates a tabular form, updatable columns are displayed, by default, as text fields. In the next exercise, you change the default display of the Department Id column to a select list. To accomplish this, you create a named list of values (LOV) and then edit the column attributes.

Topics in this section include:

See Also:

"Creating Lists of Values" in Oracle Database Application Express User's Guide

Create a Named List of Values

To create a named LOV for the Department Id:

  1. Click Edit Page 2 on the Developer toolbar as shown in Figure 2-3.

    Figure 2-3 Developer Toolbar

    Description of Figure 2-3 follows
    Description of "Figure 2-3 Developer Toolbar"

    The Page Definition for page 2 appears.

  2. Under List of Values, click the Create icon.

    The Create List of Values Wizard appears.

  3. For Source, select From Scratch and click Next.

  4. For Name and Type:

    1. Name - Enter DEPTID.

    2. Type - Select Dynamic.

    3. Click Next.

  5. For Query or Static Values, replace the existing text with this:

    SELECT DISTINCT department_id a, department_id b FROM oehr_employees
    
  6. Click Create List of Values.

    The Page Definition for page 2 appears. Note that the LOV does not yet appear on the Page Definitions.

Edit the Column to Display as a Select List

To edit the column to display as a select list:

  1. Under Regions, click the Report link.

    The Report Attributes page appears as shown in Figure 2-5.

    Figure 2-5 Column Attributes on the Report Attributes Page

    Description of Figure 2-5 follows
    Description of "Figure 2-5 Column Attributes on the Report Attributes Page"

  2. Under Column Attributes, click the Edit icon next to the DEPARTMENT_ID column as shown in Figure 2-5. The Edit icon resembles a small page with a pencil on top of it.

    The Column Attributes page appears.

    Next, change the default display of this column to a select list.

  3. Scroll down to Tabular Form Element. From Display As, select Select List (named LOV).

  4. Scroll down to Lists of Values. From Named LOV, select DEPTID.

  5. Scroll up to the top of the page and click Apply Changes.

  6. Click the Run Page icon in the upper right corner of the page.

    As shown in Figure 2-6, notice the Department Id column now displays as a select list.

    Figure 2-6 Tabular Form with Department Id Column Changed to a Select List

    Description of Figure 2-6 follows
    Description of "Figure 2-6 Tabular Form with Department Id Column Changed to 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 the data in the form and applying updates.

Consider the following example:

SELECT first_name FROM oehr_employees;

Note that this should not be altered to:

SELECT lower(first_name) FROM oehr_employees