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 22.214.171.124 or later) or register for a workspace online at htmldb.oracle.com.
To register for a workspace at htmldb.oracle.com:
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:
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:
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:
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:
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:
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:
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:
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:
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).
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:
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:
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:
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:
Click on the traffic light icon at the top right of the page to run the current page.
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(email@example.com) is a senior product manager at Oracle.