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:

Navigate to the appropriate Page Definition. See "Accessing a Page Definition".
Create an HTML region:
1. Under Regions, click the Create icon.
2. Select the region type HTML.
3. Follow the on-screen instructions.
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
Creating a Process that Contains One or More Insert Statements
Using a PL/SQL API to Process Form Values

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:

Navigate to the appropriate Page Definition. See "Accessing a Page Definition"
Under Processes, click the Create icon.
Select the process Data Manipulation.
Select the process category Automatic Row Processing (DML).
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).
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:

Navigate to the appropriate Page Definition. See "Accessing a Page Definition".
Under Processes, click Create.
Select the process type Data Manipulation.
Select the process category Automatic Row Fetch.
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.
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.