As Published In
Oracle Magazine
January/February 2005

DEVELOPER: Application Express


Wizards Creating Wizards

By Sergio Leunissen

Use Oracle Application Express (formerly Oracle HTML DB) to deliver wizard-based data entry forms.

When your application is collecting a lot of data on the Web in one session, it's often helpful to break the data entry form into a series of pages forming a wizard. Such a data entry wizard is especially useful when you're collecting many data elements or when the data collection task is complex and not frequently performed by the user. If you are a Web developer who needs to build data entry forms that span multiple pages, you can make your job easier by using Oracle Application Express's built-in session state management and page flow.

Oracle Application Express helps you quickly build reporting and data entry applications for the Web. In a previous Oracle Magazine article, "Serving HTML DB Reports," I explained the steps needed to build flexible reports. In this article, I'll explain how to build wizard-based data entry screens that insert data into the database.

Building a Wizard

In this example, you'll build a wizard that takes the user through the task of requesting an insurance policy for a vehicle. The vehicle can be either a car or a boat. Depending on the vehicle type chosen by the user, different data elements need to be collected—for example, the Vehicle Identification Number (VIN) for a car and hull length for a boat.

The steps described in this article apply to version 1.6 of Oracle Application Express.

Step 1: Get access to an Oracle Application Express workspace. To get started, you need access to an HTML DB workspace. You can either download and install Oracle Application Express 1.6 in your own database (Oracle9i Database 9.2.0.3 or later) or register for a workspace online at htmldb.oracle.com.

To register for a workspace at htmldb.oracle.com:

  1. In the Tasks region, click on the Request a Workspace link.

  2. Click on the Next button.

  3. In the First Name field, enter your first name.

  4. In the Last Name field, enter your last name.

  5. In the Email field, enter a valid e-mail address, to which your login credentials will be sent after the workspace request is approved.

  6. Click on the Next button.

  7. In the Workspace field, enter a unique workspace name. If the name you enter is already taken, you will be asked to enter another.

  8. Click on the Next button.

  9. Click on the Request a New Schema radio button.

  10. Enter a unique schema name.

  11. For Initial Space Allocation, click on the Small: 2 Megabytes radio button.

  12. In the Description field, enter why you are requesting the workspace—for example, Testing the Oracle Magazine Step-by-Step Article .

  13. Click on the Next button.

  14. Review your selections, and click on the Accept button.

You can log in to your new workspace when you receive your login credentials via e-mail. From your installation or htmldb.oracle.com , log in to your workspace.

Step 2: Create an application. Now create a new application. The application is a collection of pages to which you can add components such as forms and reports. To start out, you'll create only one page. This is because you'll be using a wizard that will automatically add new pages to the application.

To create an application:

  1. From your workspace home page, click on the Create Application button.

  2. Click on the Next button.

  3. In the Name field, enter Oracle Magazine Step by Step.

  4. From the Pages drop-down list, choose 1.

  5. Click on the Next button.

  6. Click on the No Tabs radio button.

  7. Click on the Next button.

  8. In the Page Name field, type Welcome.

  9. Click on the Next button.

  10. Choose a theme to your taste from radio buttons 1 through 12.

  11. Click on the Next button.

  12. Click on the Create Application button.

The application has been created. To run it, click on the traffic light icon. You'll be presented with a login page. Use the same username and password that you used to log in to your workspace. You'll be presented with the one page in the application (not counting the login page).

To get ready for the next step, in the Developer toolbar at the bottom of the page, click on the Edit Application link.

Step 3: Create database tables. To store information about insurance policies, create a table. For the sake of brevity, you'll use a single table to store information about policies and a single lookup table to illustrate the use of a table-driven list of values. Listing 1 shows the script used to create the ORAMAG_POLICIES table, the BOAT_MAKERS lookup table with seed data, as well as a supporting sequence and trigger. You can run these scripts in Oracle Application Express's SQL Workshop.

Code Listing 1: Script to create ORAMAG_POLICIES and more 

CREATE TABLE "ORAMAG_POLICIES" 
  (     "ID" NUMBER, 
        "FIRST_NAME" VARCHAR2(255), 
        "LAST_NAME" VARCHAR2(255), 
        "VEHICLE_TYPE" VARCHAR2(4000), 
        "ZIP" VARCHAR2(12), 
        "CAR_MAKE" VARCHAR2(4000), 
        "CAR_MODEL" VARCHAR2(4000), 
        "HULL_LENGTH" VARCHAR2(4000), 
        "HORSEPOWER" VARCHAR2(4000), 
        "MODEL_YEAR" VARCHAR2(4), 
        "VIN" VARCHAR2(25), 
        "BOAT_MAKE" NUMBER, 
        "BOAT_MODEL" VARCHAR2(4000), 
        "EMAIL" VARCHAR2(255), 
         CONSTRAINT "ORAMAG_POLICIES_PK" PRIMARY KEY ("ID") ENABLE
  )
/
CREATE SEQUENCE "ORAMAG_POLICIES_SEQ"
/
CREATE OR REPLACE TRIGGER "BI_ORAMAG_POLICIES" 
 before insert on "ORAMAG_POLICIES"       
 for each row 
begin 
 for c1 in ( 
  select ORAMAG_POLICIES_SEQ.nextval next_val 
  from dual 
 ) loop 
  :new.ID := c1.next_val; 
 end loop; 
end;
/
CREATE TABLE "BOAT_MAKERS" (
  "ID" NUMBER,
  "BOAT_MAKE" VARCHAR2(4000),
  constraint "BOAT_MAKERS_PK" primary key ("ID")
)
/
insert into boat_makers (id, boat_make)
values (1, 'Benetau')
/
insert into boat_makers (id, boat_make)
values (2, 'Swann')
/
insert into boat_makers (id, boat_make)
values (3, 'Halberg-Rassy')
/
insert into boat_makers (id, boat_make)
values (4, 'J-Boats')
/
insert into boat_makers (id, boat_make)
values (5, 'Bertram')
/

To create the necessary database objects, using the SQL Workshop:

  1. Click on the SQL Workshop tab.

  2. Under SQL Scripts, click on the Scripts button.

  3. Click on the Create button.

  4. In the Script Name field, type a name; for example, create policies table.

  5. Click on the Next button.

  6. Copy and paste the contents of Listing 1 into the Script text area.

  7. Click on the Create Script button.

  8. For the script you've just created, click on the Run link in the Actions column.

  9. Click on the Run Script button.

All the database objects necessary for this article have now been created.

Step 4: Create the wizard outline. Oracle Application Express provides wizards to add components such as a report or a chart to a page and wizards that create pages. One of these wizards helps you create your own wizards.

A wizard consists of two or more pages with form fields and buttons on each page, including the necessary page flow logic to step the user forward and backward through the wizard screens. For this wizard, you'll create four wizard screens: Vehicle Type, Boat Details, Car Details, and Personal Details.

To create the wizard outline:

  1. Click on the Application Builder tab.

  2. Click on the Create Page button.

  3. Click on the Page with Component radio button.

  4. Click on the Next button.

  5. Click on the Wizard radio button.

  6. Click on the Next button.

  7. Click on the Next button.

  8. From the Create This Number of Pages drop-down list, choose 4.

  9. From the Create Information Region for Each Page? drop-down list, choose No.

  10. Click on the Next button.

  11. From the Region Template drop-down list, choose Wizard Region.

  12. Click on the Next button.

  13. Click on the Do Not Use Tabs radio button.

  14. Click on the Next button.

  15. The Pages have the values 2, 3, 4, and 5. Enter the following in the Page Name fields for these values, in order: Vehicle Type, Boat Details, Car Details, Personal Details.

  16. From the Items drop-down list, choose the following number of items, in order: 1, 4, 4, and 4.

  17. Click on the Next button.

  18. Click on the Next button.

  19. In the When Finish Button Pressed Branch to This Page field, type 1.

  20. In the When Cancel Button Pressed Branch to This Page field, type 1.

  21. Click on the Next button.

  22. Click on the Finish button.

Now click on theRun Pagebutton to verify that the wizard was created.

Step 5: Change text item to radio group. The first step in the sample application wizard asks users whether they want to insure a boat or a car. The first wizard step has been created with one text field item. You'll need to change the item type from a text field to a radio group.

To change the text item:

  1. At the bottom of the first page, in the Developer toolbar, click on the Edit Application link.

  2. In the Application Builder, next to page 2, click on the Vehicle Type link.

  3. Under Page Rendering -> Items, click on the P2_ITEM1 link.

  4. In the Name field, change P2_ITEM1 to P2_VEHICLE_TYPE.

  5. From the Display As drop-down list, choose Radiogroup.

  6. In the Label field, change the current label, ITEM 1 , to What are you insuring? .

  7. Scroll down to the List of Values attributes.

  8. In the List of Values definition field, type STATIC:Boat;BOAT,Car;CAR.

  9. Click on the button.

  10. Click on the traffic light icon at the top right of the page to run it. The content of the first page of the wizard looks like that in Figure 1, but the theme for your application is the one you chose in Step 2.



figure 1
Figure 1: First page of insurance policy application wizard


The first wizard step has now been completed.

Step 6: Set up conditional page flow from the Vehicle Type page. Depending on the vehicle type selected in the first step of your wizard, you need to collect different data in a subsequent step. To do this, set up the page flow so that it is conditional, based on the value of the radio group item.

Page flow in Oracle Application Express applications is controlled by branches. You'll be editing the existing branch to navigate to the Boat Details page (page 3), and you'll also add a branch to take users to the Car Details page (page 4) when they choose Car and click on the Next button.

To modify the existing branch and add a new one:

  1. In the Developer toolbar, click on the Edit Page 2 link.

  2. Under Page Processing -> Branches, click on the Go To Page link. (Note: The "3" that follows "Go To Page" is a separate link.)

  3. Scroll down to the Conditional Branching section.

  4. From the Condition Type drop-down list, choose Value of Item in Expression 1 = Expression 2.

  5. In the Expression 1 field, type P2_VEHICLE_TYPE.

  6. In the Expression 2 field, type BOAT.

  7. Click on the Apply Changes button.

  8. Under Page Processing -> Branches, click on the Create icon (+).

  9. Click on the Next button.

  10. In the Page field, type 4.

  11. Click on the Next button.

  12. From the When Button Pressed drop-down list, choose NEXT.

  13. From the Condition Type drop-down list, choose Value of Item in Expression 1 = Expression 2.

  14. In the Expression 1 field, type P2_VEHICLE_TYPE.

  15. In the Expression 2 field, type CAR.

  16. Click on the Create Branch button.

  17. At the top right of the page, click on the traffic light icon to run the updated wizard.

Test the page flow by choosing the Car radio button and clicking on the Next button. You should now be on page 4, Car Details. Click on the Next button once more, and you're on the Personal Details page. From here, you can adjust the page flow backward.

Step 7: Modify reverse page flow for the Personal Details page. To modify page flow from the Personal Details page:

  1. In the Developer toolbar, click on the Edit Application link.

  2. Next to Page 5, click on the Personal Details link.

  3. Under Page Processing -> Branches, for the branch that goes to page 4, click on the Go To Page link.

  4. Under Conditional Branching, from the Condition Type drop-down list, choose Value of Item in Expression 1 = Expression 2.

  5. In the Expression 1 field, type P2_VEHICLE_TYPE.

  6. In the Expression 2 field, type CAR.

  7. Click on the Apply Changes button.

  8. Under Page Processing -> Branches, click on the Create icon (+).

  9. Click on the Next button.

  10. In the Page field, type 3.

  11. Click on the Next button.

  12. From the When Button Pressed drop-down list, choose PREVIOUS.

  13. From the Condition Type drop-down list, choose Value of Item in Expression 1 = Expression 2.

  14. In the Expression 1 field, type P2_VEHICLE_TYPE.

  15. In the Expression 2 field, type BOAT.

  16. Click on the Create Branch button.

  17. At the top right of the page, click on the traffic light icon to run the updated wizard.

  18. Click on the Previous button and make sure you are on page 4, Car Details.

Step 8: Modify page flow back for the Car Details and Boat Details pages. When users land on page 4, Car Details, and click on the Previous button, they currently are taken to page 3, Boat Details. Change this flow to branch to page 2 instead. Similarly, when users click on the Next button while on the Boat Details page, they are taken to Page 4, Car Details. Change this behavior so that the users skip the Car Details page and go directly to the Personal Details page, page 5.

To change the page flow for page 3 and page 4:

  1. At the bottom of page 4 in the Developer toolbar, click on the Edit Page 4 link.

  2. Under Page Processing -> Branches, next to the number 3, click on the Go To Page link.

  3. In the Page field, change the value 3 to 2.

  4. Click on the Apply Changes button.

  5. Near the top left of the Page Definition page, click on < to navigate to page 3, or enter 3 in the Page field and click on the Go button.

  6. Under Page Processing -> Branches, click on the Go To Page link to edit the branch that currently goes to page 4.

  7. In the Page field, change the value 4 to 5.

  8. Click on the Apply Changes button.

Step 9: Modify the Boat Details form. There are four pieces of information you need to collect about a boat to insure it: make, model, total horsepower, and hull length. The form that was generated on page 3 consists of four text items. Modify these text items to have appropriate names and labels. Item names are unique within an application and are used to reference session state values.

To modify the Boat Details form:

  1. Navigate to page 3 in the Application Builder.

  2. Under Page Rendering -> Items, click on the P3_ITEM1 link.

  3. In the Name field, change P3_ITEM1 to P3_BOAT_MAKE.

  4. From the Display As drop-down list, choose Select List.

  5. In the Label field, type Make.

  6. Under List of Values, in the List of values definition field, type select boat_make, id from boat_makers.

  7. Click on the Apply Changes button.

  8. Under Page Rendering -> Items, click on the P3_ITEM2 link.

  9. In the Name field, change P3_ITEM2 to P3_BOAT_MODEL.

  10. In the Label field, type Model.

  11. Click on the Apply Changes button.

  12. Under Page Rendering -> Items, click on the P3_ITEM3 link.

  13. In the Name field, change P3_ITEM3 to P3_TOTAL_HP.

  14. In the Label field, type Total Horsepower.

  15. Under Element, in the Width field, change 30 to 4.

  16. Click on the Apply Changes button.

  17. Under Page Rendering -> Items, click on the P3_ITEM4 link.

  18. In the Name field, change P3_ITEM4 to P3_HULL_LENGTH.

  19. In the Label field, type Hull Length.

  20. Click on the Apply Changes button.

The Boat Details form is complete.

Step 10: Modify the Car Details form. There are four pieces of information you need to collect about a car to insure it: make, model, model year, and Vehicle Identification Number (VIN).

  1. Near the top left of the Page Definition page, enter 4 in the Page field and click on the Go button.

  2. Under Page Rendering -> Items, click on the P4_ITEM1 link.

  3. In the Name field, change P4_ITEM1 to P4_CAR_MAKE.

  4. In the Label field, type Make.

  5. Click on the Apply Changes button.

  6. Under Page Rendering -> Items, click on the P4_ITEM2 link.

  7. In the Name field, change P4_ITEM2 to P4_CAR_MODEL.

  8. In the Label field, type Model.

  9. Click on the Apply Changes button.

  10. Under Page Rendering -> Items, click on the P4_ITEM3 link.

  11. In the Name field, change P4_ITEM3 to P4_MODEL_YEAR.

  12. In the Label field, type Model Year.

  13. Under Element, in the Width field, change 30 to 4.

  14. Click on the Apply Changes button.

  15. Under Page Rendering -> Items, click on the P4_ITEM4 link.

  16. In the Name field, change P4_ITEM4 to P4_VIN.

  17. In the Label field, type Vehicle Identification Number.

  18. Click on the Apply Changes button.

Step 11: Modify the Personal Details form. On the personal details form, collect first name, last name, e-mail, and zip code information.

To modify the Personal Details form:

  1. Near the top left of the Page Definition page, enter 5 in the Page field and click on the Go button.

  2. Under Page Rendering -> Items, click on the P5_ITEM1 link.

  3. In the Name field, change P5_ITEM1 to P5_FIRST_NAME.

  4. In the Label field, type First Name.

  5. Click on the Apply Changes button.

  6. Under Page Rendering -> Items, click on the P5_ITEM2 link.

  7. In the Name field, change P5_ITEM2 to P5_LAST_NAME.

  8. In the Label field, type Last Name.

  9. Click on the Apply Changes button.

  10. Under Page Rendering -> Items, click on the P5_ITEM3 link.

  11. In the Name field, change P5_ITEM3 to P5_EMAIL.

  12. In the Label field, type Email.

  13. Click on the Apply Changes button.

  14. Under Page Rendering -> Items, click on the P5_ITEM4 link.

  15. In the Name field, change P5_ITEM4 to P5_ZIPCODE.

  16. In the Label field, type Zip Code.

  17. Click on the Apply Changes button.

The Personal Details form is complete.

Step 12: Apply data validation. Some of the information being collected in the wizard is considered mandatory. To ensure that mandatory values are entered, use Oracle Application Express's built-in validation services. In this case, add a validation to ensure that a valid e-mail address is entered on the Personal Details page.

To add a validation:

  1. In the Application Builder, on page 5, under Validations, click on the Create icon (+).

  2. Choose the Item Level Validation radio button.

  3. Click on the Next button.

  4. Choose the P5_EMAIL radio button.

  5. Click on the Next button.

  6. Choose the SQL radio button.

  7. Click on the Next button.

  8. Choose the SQL Expression radio button.

  9. Click on the Next button.

  10. In the Validation Name field, type Check for valid email address.

  11. Click on the Next button.

  12. In the Validation field, type instr(:P5_EMAIL, '@' ) > 0 and instr (:P5_EMAIL, '.') > 0

  13. In the Error Message field, type Please enter a valid email address.

  14. Click on the Next button.

  15. From the When Button Pressed drop-down list, choose FINISH.

  16. Click on the Create button.

Step 13: Insert captured data. Once the user has stepped through the wizard and the necessary data has been collected in session state, the wizard can insert it into the database.

To enable the wizard to insert the collected data into the database:

  1. In the Application Builder, on page 5, in the Page Processing column, next to Processes click on the Create icon (+).

  2. Choose the PL/SQL radio button.

  3. Click on the Next button.

  4. In the Name field, type Insert data.

  5. Click on the Next button.

  6. In the Enter PL/SQL Page Process field, enter the code in Listing 2.

  7. Click on the Next button.

  8. In the Success Message field, type Your policy data has been captured. You will be contacted via email soon.

  9. Click on the Next button.

  10. From the When Button Pressed drop-down list, choose FINISH.

  11. Click on the Create Process button.

Code Listing 2:Insert data collected from the wizard

 

INSERT INTO oramag_policies (
      FIRST_NAME,
          LAST_NAME,
          VEHICLE_TYPE, 
          ZIP, 
          CAR_MAKE, 
          CAR_MODEL, 
          HULL_LENGTH, 
          HORSEPOWER, 
          MODEL_YEAR, 
          VIN, 
          BOAT_MAKE, 
          BOAT_MODEL, 
          EMAIL
      )
   VALUES (
      :P5_FIRST_NAME,
      :P5_LAST_NAME,
      :P2_ITEM1,
      :P5_ZIPCODE,
      :P4_CAR_MAKE,
      :P4_CAR_MODEL,
      :P3_HULL_LENGTH,
      :P3_TOTAL_HP,
      :P4_MODEL_YEAR,
      :P4_VIN,
      :P3_BOAT_MAKE,
      :P3_BOAT_MODEL,
      :P5_EMAIL
      );


Step 14: Add a button to the Welcome page. When you run the application, you start on page 1. Page 1 is currently empty. To avoid confusion, add a start button to page 1.

To add a start button:

  1. Near the top left of the Page Definition page, enter 1 in the Page field and click on the Go button.

  2. Under Page Rendering -> Regions, click on the Create icon (+).

  3. Choose the HTML radio button.

  4. Click on the Next button.

  5. In the Title field, type Welcome to the Policy Wizard.

  6. Click on the Next button.

  7. Click on the Create Region button.

  8. Under Page Rendering -> Buttons, click on the Create icon (+).

  9. Choose the Welcome to the Policy Wizard radio button.

  10. Click on the Next button.

  11. Choose the Create Button in a Region Position radio button.

  12. Click on the Next button.

  13. In the Button Name field, type START.

  14. Click on the Next button.

  15. Click on the Next button.

  16. Click on the Next button.

  17. In the Branch to Page (when button pressed) field, type 2.

  18. Click on the Create Button button.

Step 15: Test the wizard.Before you test the wizard, log out of the application and establish a new session. You need a new session because you have changed names of items, which are used to reference session state values. The item names are stored in the database and are associated with a session identifier.

Next Steps


LEARN more about Oracle Application Express
 oracle.com/technetwork/products/database/application_express
htmldb.oracle.com/studio

READ
 more Sergio

 

Click on the traffic light icon at the top right of the page to run the current page.

  1. At the top right, click on the Logout link.

  2. In the User Name and Password field, type the username and password that you used to log in to your workspace.

  3. Click on the Login button.

  4. Click on the Start button.

  5. Choose the Boat radio button.

  6. Click on the Next button.

  7. From the Make drop-down list, choose Swann.

  8. In the Model field, type Swann 56.

  9. In the Total Horsepower field, type 50.

  10. In the Hull Length field, type 56.

  11. Click on the Next button.

  12. In the First Name field, type your first name.

  13. In the Last Name field, type your last name.

  14. In the Email field, type test@validation.

  15. Click on the Finish button.

  16. Fix the email address by changing it to test@validation.com.

  17. In the Zip Code field, type your zip code.

  18. Click on the Finish button.

  19. In the Developer toolbar, click on the Edit Application link.

  20. Click on the SQL Workshop tab.

  21. Click on SQL Command Processor.

  22. Enter the following SQL statement:

     

    SELECT first_name, last_name, email, zip, boat_makers.boat_make, boat_model 
      FROM oramag_policies,boat_makers
      WHERE boat_makers.id = 
            oramag_policies.boat_make
    
  23. Click on the Run SQL button.
The data you entered appears in the SQL query results screen.

Conclusion

In this article, you've seen how using Oracle Application Express can help you build wizard-based data entry forms. Conditional flow through a series of subtasks in the wizard can easily be incorporated without scripting or writing code. You started out with a wizard outline generated by the application builder. Then you modified form fields to capture the appropriate data and modified the generated page flow to respond to user selections. To finish the application, you added form validation and logic for inserting the captured data in the database.


Sergio Leunissen(sergio.leunissen@oracle.com) is a senior product manager at Oracle.