11.4 Creating a Form Manually in Component View

11.4.2.1 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 must:

• 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. See "Viewing a Page in Component View"

2. Access the Create Page Process Wizard. Under Processes, click the Create icon.

3. For Process Type, select Data Manipulation and click Next.

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

5. For Process Attributes:

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

   2. Sequence - Specify a sequence number.

   3. Point - Choose the point of execution for this process (for example, Onload - After Header).

   4. Type - Displays the selected process type.

   5. Click Next.

6. Follow the on-screen instructions.

11.4.2.2 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;

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