Previous
Previous
 
Next
Next

Creating a Form Manually

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

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:

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:

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:

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: