Creating a Tabular Form

About Tabular Forms

There are two primary methods for creating tabular forms:

• Create Application Wizard

• Create Page Wizard

Tabular forms created using these wizards include automatic DML processes. These wizards create a form that performs update, insert, and delete operations on multiple rows in a database table. Alternatively, you can build a tabular form manually either by creating a updatable report region or standard report region with updatable columns in conjunction with custom PL/SQL page processes to process the tabular form data.

Running a Tabular Form

When running a tabular form, data is rendered as a report having updateable columns shown using various form elements (including text fields, text areas, date pickers, select lists, radio groups, and so on).

Description of the illustration rprt_tabular_form.gif

By default, date columns are created using the Date Picker item type. If user interface defaults are defined, other columns can be automatically created as select lists based on lists of values, or numeric fields and large text areas.

See Also: "Managing User Interface Defaults" in Oracle Application Express SQL Workshop Guide

For numeric and date fields, you can also pre-define date and number format masks, or apply those format masks after generating the initial form.

Each report also includes a check box column (called a row selector). Users use this row selector to select one or more columns and update or delete data. To add a row, users click Add Row.

Tip: You can control the appearance of a specific column by editing column attributes. See "Altering Classic Report Layout Using Column Attributes" and "Editing SQL and Classic Reports".

How Tabular Forms Work

Tabular forms are based on a SQL query in which the query columns are tied to the underlying table columns. Unlike a single record from, tabular forms do not store data in session state. When a tabular form page is submitted, the tabular form data is processed using built-in data manipulation language (DML), or a custom PL/SQL page processes.

A tabular form with automatic DML has two processes for multi-row operations:

• A Multi Row Update (MRU) process performs create and update operations

• Multi Row Delete (MRD) process deletes requests on one or more rows

MRU and MRD processes reference the underlying table name and the primary key columns. The primary key columns must be part of the tabular form SQL query, and the report columns must be either hidden or display-only (save state) in order for the DML processes to be able to identify the correct records. For new records, the primary key values must be NULL in order to be identified as new records. Delete operations are performed by referencing row number of the row to be deleted. Users can check one or more rows on the current page and when they press the Delete button, the row numbers are used to identify the corresponding primary key value(s) and the matching rows are deleted.

Wizard generated tabular forms built-in DML, include a checksum column to prevent lost updates. Using a method called optimistic locking, a checksum is computed for each row when rendering a tabular form. This checksum is stored in an extra hidden tabular form column. Upon submission of the form for update, an additional set of checksums is computed on the then current data stored in the database. The new checksums are compared to those built when the tabular form was first rendered, and if there are any mismatches, the update is rejected, because the data in the database was either modified by another user or anther process in the time between the tabular form was rendered and the time the data was submitted.

Wizard generated tabular forms also automatically create validations for some columns. Validations are created for columns that are set to NOT NULL in the underlying table and columns of type NUMBER, DATE or TIMESTAMP. Note that validations are not created for columns if the column is set to read-only, either based on the user's selection, or defined user interface defaults.

Creating a Tabular Form Using the Create Application Wizard

The Create Application Wizard is designed to quickly create a basic Oracle Application Express application consisting of multiple pages and components. For that reason, this wizard uses general best practices to generate the application and does not include several advanced options included in the Create Page Wizard.

To create a tabular form using the Create Application Wizard:

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

2. Click the Create button.

3. For Application Type, select Database.

4. For Method, select From Scratch.

5. For Name, enter the following and click Next:

   1. Name - Enter a name to identify the application.

   2. Application - Enter a unique integer value to identify the application.

   3. Create Application - Select a creation method:

      • Select From scratch to manually add all pages

      • Select Based on existing application design model to copy page definitions from a previous application model.

        Note that you will still have to define all other application attributes, or you can choose to copy some attributes by choosing to copy shared components from another application (See step 11).

   4. Schema - Your application will obtain its privileges by parsing all SQL as a specific database schema. Identify the database schema owner.

   Next, select a page type and table on which to build the tabular form.

6. For Pages:

   1. Select Page Type - Select Tabular Form.

   2. Table Name - Select a table.

      Select the operations to be performed on the table (for example, Update, Insert and Delete).

   3. Click Add Page.

      The new page appears at the top of the page.

   4. Click Next.

7. On Tabs, specify a tab implementation and click Next.

8. For Shared Components, determine whether to import shared components from another application. Shared components are common elements that can display or be applied on any page within an application.

   To include shared components, select the following:

   1. Copy Shared Components from Another Application - Select Yes.

   2. Copy from Application - Select the application from which you want to import shared components.

   3. Select Components to Import - Select the components to import.

   4. Click Next.

   Next, select a default authentication scheme. Authentication is the process of establishing a user's identity before they can access an application. See "Establishing User Identity Through Authentication".

9. For Authentication Scheme, select one of the following:

   • Application Express - Uses the user account credentials created and maintained with the Application Express Service Administration application. These are the accounts you use to log in to the Application Express development environment. You can also create accounts in this user account repository for end users of your applications.

   • No Authentication - Also known as database authentication, this option enables users to access your application using the account credentials stored in the modplsql DAD definition. In most cases this results in users not having to login when accessing your application. This is the quickest way to create a "public" application.

   • Database Account - Requires users logging into your application to enter a database schema name (or user name) and a password in order to authenticate. This account information is managed entirely within the Oracle database.

10. Next, specify the following attributes:

    1. Language - Select the primary language for this application.

       This attribute identifies the language in which an application is developed. This language is the base language from which all translations are made.

    2. User Language Preference Derived From - Specifies how the engine determines the application language. The application primary language can be static (that is, derived from the Web browser language) or determined from a user preference or item. The database language setting determines date display and sorting characteristics.

       You can alter the Language and User Language Preference Derived From attributes later on the Edit Globalization attributes page. See "Configuring Globalization Attributes".

    3. Date Format - Specifies the date format to be used in the application.

       This date format is used to alter the NLS_DATE_FORMAT database session setting before showing or submitting any page within the application. This value can be a literal string containing a valid Oracle date format mask or an item reference via substitution syntax. If no value is specified, the default date format is derived from the database session at runtime. If supplied, this is also used as the date format for any items resulting from columns of type DATE.

    4. Click Next.

11. For User Interface, select a theme and click Next.

    Themes are collections of templates that can be used to define the layout and style of an entire application. See "Managing Themes".

12. Confirm your selections and click Create.

Creating a Tabular Form Using the Create Page Wizard

Use the Create Page Wizard to add a tabular form to an existing application. Once you provide a table name on which to build the tabular form, the wizard reads the table columns from the database data dictionary. You then choose which columns to include in the tabular form, either as display-only columns or as updatable columns. Unlike the Create Application Wizard, you can choose between three different primary key source types: trigger, PL/SQL function, and sequence.

To create a tabular form using the Create Page Wizard:

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

2. Select an application.

3. Click Create Page.

4. Select Form and click Next.

5. Select Tabular Form and click Next.

   The Create Tabular Form Wizard appears.

6. For Table/View Owner:

   1. Specify the table or view owner on which you want to base your tabular form.

   2. Select the operations to be performed on the table (for example, Update, Insert and Delete).

   3. Click Next.

7. For Table/View Name, select a table and click Next.

8. For Displayed Columns:

   1. Select the columns (updatable and nonupdatable) to include in the form.

      Note that you can modify the column order or your SQL query after you create the page.

   2. Click Next.

9. For Primary Key, select the Primary Key column and a secondary Primary Key column (if applicable) and click Next.

10. For Primary Key Source, select a source type for the primary key column and click Next. Valid options include:

    • Existing trigger - Select this option if a a before-insert trigger populates the primary key. You can also select this option if you plan on specifying the primary key column source later after completing the form.

    • Custom PL/SQL function - Select this option if you want to provide a PL/SQL function to generate returning key value.

    • Existing sequence - Select this option if you want to pick the sequence from a list of sequences available in the selected schema.

11. On Updatable Columns, select which columns should be updatable and click Next.

12. On Page and Region Attributes:

    1. Specify a page name and region information.

    2. Specify a region title.

    3. Select a region template.

    4. Select a report template.

    5. Determine whether to include breadcrumbs.

    6. Click Next.

13. On Tab, specify a tab implementation for this page and click Next.

14. On Button Labels, enter the display text to appear for each button and click Next.

15. On Branching, specify the pages to branch to after the user clicks the Submit and Cancel buttons and click Next.

16. Click Finish.

    A success message appears.