Creating Forms

You can include a variety of different types of forms in your applications. You can include forms that enable users to update just a single row in a table or multiple rows at once. Application Builder includes a number of wizards you can use to create forms automatically, or you can create forms manually.

Topics in this section include:

Creating a Form Using a Wizard

The easiest way to create a form is to use a wizard. For example, the Form on Table or View Wizard creates one item for each column in a table. It also includes the necessary buttons and processes required to insert, update, and delete rows from the table using a primary key. Each region has a defined name and display position; all other attributes are items, buttons, processes, and branches.

To create a form using a 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. Under Forms, select a type of form page as described in Table 7-6.

    Table 7-6 Forms Page Types

    Form Page Type Description

    Form on a Procedure

    Builds a form based on stored procedure arguments. Use this approach when you have implemented logic or data manipulation language (DML) in a stored procedure or package.

    Form on a Table or View

    Creates a form that enables users to update a single row in a database table.

    Form on a Table with Report

    Creates two pages. One page displays a report. Each row provides a link to the second page to enable users to update each record.

    Note: This wizard does not support tables having more than 127 columns. Selecting more than 127 columns generates an error.

    Master Detail Form

    Creates a form that displays a master row and multiple detail rows within a single HTML form. With this form, users can query, insert, update, and delete values from two tables or views.

    See Also: "Creating a Master Detail Form"

    Tabular Form

    Creates a form in which users can update multiple rows in a database.

    See Also: "Creating a Tabular Form"

    Form on a SQL Query

    Creates a form based on the columns returned by a SQL query such as an EQUIJOIN.

    Summary Page

    Creates a read-only version of a form. Typically used to provide a confirmation page at the end of a wizard.

    Form on Web Service

    Creates a page with items based on a Web service definition. This wizard creates a user input form, a process to call the Web service, and a submit button.

    See Also: "Creating a Form on a Web Service"

    Form and Report on Web Service

    Creates a page with items based on a Web service definition. This wizard creates a user input form, a process to call the Web service, a submit button, and displays the results returned in a report.

    See Also: "Creating an Input Form and Report on a Web Service"

  6. Follow the on-screen instructions. To learn more about a specific field, click the item label.

    When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

Creating a Tabular Form

A tabular form enables users to update multiple rows in a table. The Tabular Form Wizard creates a form to perform update, insert, and delete operations on multiple rows in a database table.

To create a tabular form:

  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 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 trigger is defined for the table. 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 page and region information.

    2. Select a region template.

    3. Select a report template.

    4. 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.

Note:

Any modification of the select list of a SQL statement of a tabular form after it has been generated is not recommended. If you do modify the query, make sure the values of the updateable columns are not altered after being queried from the database by the Application Express engine.

See Also:

"Managing User Interface Defaults"

See Tutorial:

"How to Create a Tabular Form" in Oracle Application Express Advanced Tutorials

Creating a Master Detail Form

A master detail form reflects a one-to-many relationship between two tables in a database. Typically, a master detail form displays a master row and multiple detail rows within a single HTML form. With this form, users can insert, update, and delete values from two tables or views.

To create a master detail form:

  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 Master Detail Form and click Next.

    The Master Detail Wizard appears.

  6. On Master Table:

    1. Select the table or view owner.

    2. Select a table or view name.

      The columns in that object appear under Available Columns.

    3. Select the columns to display in the form and then click the arrow keys to move them to Displayed Columns.

    4. Click Next.

  7. On Detail Table:

    1. Specify whether to show only related tables by selecting Yes or No.

    2. Select the table or view owner.

    3. Select a table or view name.

      The columns in that object appear under Available Columns.

    4. Select the columns to display in the form and then click the arrow keys to move them to Displayed Columns.

    5. Click Next.

  8. On Primary Key:

    1. Primary Key(s) of Master Table - Select the primary key (or keys) of the master table. You can select maximum of two primary keys for master table.

    2. Primary Key(s) of Detail Table - Select the primary key (or keys) of the detail table. You can select maximum of two primary keys for detail table.

    3. Click Next.

  9. On Master Detail Link, define the relationship between the master and detail tables and click Next.

  10. On Primary Key Source, select the primary key column for the master table and click Next. Then select the primary key column for the detail table and click Next. Options include:

    • Existing trigger - Select this option if a trigger is defined to populate the primary key, or if the user will be expected to enter the primary key value manually. Detail tables do not support user entered primary key values.

    • Custom PL/SQL function - Select this option if you want to provide a PL/SQL function to populate the primary key.

    • Existing sequence - Select this option if an existing sequence will be used to generate the primary key.

  11. On Master Options, specify whether to include master row navigation and click Next.

    If you include master row navigation, define navigation order columns. If a navigation order column is not defined, the master update form navigates by the primary key column.

  12. On Layout, specify how to build the master detail and click Next. Options include:

    • Edit detail as tabular form on same page creates a two page master detail. Edit detail on separate page creates a three page master detail.

    • Edit detail on separate page creates a three page master detail.

  13. On Page Attributes, review and edit the master page and detail page information and then click Next.

  14. On Tab, specify whether to include a tab set and click Next.

  15. Click Create.

Creating a Form Manually

You can also create a form manually by performing the following steps:

  • Create an HTML region (to serve as a container for your page items)

  • Create items to display in the region

  • Create processes and branches

To create a form manually by creating and HTML region:

  1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition".

  2. Create an HTML region:

    1. Under Regions, click the Create icon.

    2. Select the region type HTML.

    3. Follow the on-screen instructions.

  3. Start adding items to the page:

    • Under Items, click the Create icon.

    • Follow the on-screen instructions.

Processing a Form

Once you create a form, the next step is to process the data a user types by inserting into or updating the underlying database tables or views. There are three ways to process a form:

Creating an Automatic Row (DML) Processing Process

One common way to implement a form is to manually create an Automatic Row Processing (DML) process. This approach offers three advantages. First, you are not required to provide any SQL coding. Second, Oracle Application Express performs DML processing for you. Third, this process automatically performs lost update detection. Lost update detection ensures data integrity in applications where data can be accessed concurrently.

To implement this approach you need to:

  • Add items, define the Item Source Type as Database Column, and specify a case-sensitive column name.

  • Select the option Always overrides the cache value.

To create an Automatic Row Processing (DML) process:

  1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition"

  2. Under Processes, click the Create icon.

  3. Select the process Data Manipulation.

  4. Select the process category Automatic Row Processing (DML).

  5. Specify the following process attributes:

    1. In the Name field, enter a name to identify the process.

    2. In the Sequence field, specify a sequence number.

    3. From the Point list, select the appropriate processing point. In most instances, select Onload - After Header.

    4. From the Type list, select Automated Row Processing (DML).

  6. Follow the on-screen instructions.

Creating a Process that Contains One or More Insert Statements

In this approach to form handling, you create one or more processes to handle insert, update, and delete actions. Instead of having the Application Express engine handling everything transparently, you are in complete control.

For example, suppose you have a form with three items:

  • P1_ID - A hidden item to store the primary key of the currently displayed row in a table.

  • P1_FIRST_NAME - A text field for user input.

  • P1_LAST_NAME - A text field for user input.

Assume also there are three buttons labeled Insert, Update, and Delete. Also assume you have a table T that contains the columns id, first_name, and last_name. The table has a trigger that automatically populates the ID column when there is no value supplied.

To process the insertion of a new row, you create a conditional process of type PL/SQL that executes when the user clicks the Insert button. For example:

BEGIN
  INSERT INTO T ( first_name, last_name )
     VALUES  (:P1_FIRST_NAME, :P1_LAST_NAME);
END;

To process the updating of a row, you create another conditional process of type PL/SQL. For example:

BEGIN
    UPDATE T
       SET first_name = :P1_FIRST_NAME,
           last_name = :P1_LAST_NAME
    WHERE ID = :P1_ID;
END;

To process the deletion of a row, you create a conditional process that executes when the user clicks the Delete button. For example:

BEGIN
    DELETE FROM T
    WHERE ID = :P1_ID;
END;

Using a PL/SQL API to Process Form Values

For certain types of applications, it is appropriate to centralize all access to tables in a single or a few PL/SQL packages. If you created a package to handle DML operations, you can call procedures and functions within this package from an After Submit PL/SQL process to process insert, updates, and delete requests.

Populating Forms

Oracle Application Express populates a form either on load or when the Application Express engine renders the page. You can populate a form in the following ways:

  • Create a process and define the type as Automated Row Fetch.

  • Populate the form manually by referencing a hidden session state item.

To create an Automated Row Fetch process:

  1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition".

  2. Under Processes, click Create.

  3. Select the process type Data Manipulation.

  4. Select the process category Automatic Row Fetch.

  5. Specify the following process attributes:

    1. In the Name field, enter a name to identify the process.

    2. In the Sequence field, specify a sequence number.

    3. From the Point list, select the appropriate processing point.

    4. From the Type list, select Automated Row Fetch.

  6. Follow the on-screen instructions.

You can also populate a form manually by referencing a hidden session state item. For example, the following code in an Oracle Application Express process of type PL/SQL would set the values of ename and sal. The example also demonstrates how to manually populate a form by referencing a hidden session state item named P2_ID.

FOR C1 in (SELECT ename, sal
FROM emp WHERE ID=:P2_ID)
LOOP     
     :P2_ENAME := C1.ename;
     :P2_SAL := C1.sal;
END LOOP;

In this example:

  • C1 is an implicit cursor.

  • The value of P2_ID has been set.

  • The process point for this process would be set to execute (or fire) on or before Onload - Before Regions.

Validating User Input in Forms

You can use validations to check data a user enters before processing. Once you create a validation and the associated error message, you can associate it with a specific item. You can choose to have validation error messages display inline (that is, on the page where the validation is performed) or on a separate error page.

Creating an inline error message involves these steps:

  • Create a validation and specify error message text.

  • Associate the validation with a specific item.

Creating a Validation

To create a validation:

  1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition".

  2. Under Validations, click the Create icon.

  3. When the Create Validations Wizard appears, follow the on-screen instructions.

    Validation Types are divided into two categories:

    • Item. These validations start with the term Item and provide common checks you may want to perform on the item with which the validation is associated.

    • Code. These validations require that you provide either a piece of PL/SQL code or SQL query that defines the validation logic. Use this type of validation to perform custom validations that require verifying values of more than one item or accessing additional database tables.

  4. Follow the on-screen instructions.

Note:

Validations cannot contain more than 3,950 characters.

Associating a Validation with a Specific Item

To associate an item with a validation and specify error message text:

  1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition".

  2. Under Validations, select the validation item you want to associate.

    The attributes page for the validation appears.

  3. Scroll down to Error Message:

    • In Error message display location, verify the display location.

    • In Associated Item, select the item you want to associate with this validation.

  4. Click Apply Changes.

About Error Message

Error message display location identifies where a validation error message displays. Validation error messages can display on an error page or inline within the existing page. Inline error messages can display in a notification area (defined as part of the page template) or within the field label.

To create a hard error that stops processes, including any remaining validations, you must display the error on an error page.