As Published In March/April 2006

DEVELOPER: Browser-Based

Surveying Your Application Needs

By Raj Mattamal

Oracle Application Express builds a quick survey application.

As a product manager for Oracle Application Express (formerly Oracle HTML DB), I often want to gather feedback on workshops or training sessions I deliver. Using Oracle Application Express to quickly create surveys has proven to be so useful that I figured I'd write about it. Even though this column discusses a survey I'd use for one of my sessions, you could just as easily use 10 questions of your own.

Using Oracle Application Express to create, deploy, and compile results for a survey is a natural fit, because the surveys are often needed quite quickly, distribution and access via the Web offer convenience, and results are stored in an Oracle database for easy analysis.

Step 1: Set Up the Tables

To get started, create (or have your Oracle Application Express administrator create) a new workspace. Once you're logged into your workspace, set up tables for your questions:

1. Click SQL Workshop.
2. Click Utilities.
3. Click Data Import/Export.
4. Click Import.
5. Click Import Text Data.
6. Accept the default New Table option for Import To , and select the Copy and Paste option for Import From.
7. Click Next.
8. Copy and paste the list of questions in Listing 1 into the Data field.
9. Click Next.
10. Name the table survey_questions, and click Next.
11. Accept the default primary key options, and click Import Data.

Code Listing 1: Survey questions 

Question
How would you rate the effectiveness of the overall workshop?
How would you rate the effectiveness of the overall labs?
How would you rate the presentation skills of the workshop instructor?
How would you rate the quality of the presentation materials?
How would you rate the relevance of the workshop to your job role?
The overall course materials were easy to follow.
The overall course materials matched the subject matter.
Did this workshop meet your expectations?
Please list any additional comments or suggestions for improvement:
If you rated anything a 2 or below, please provide an explanation:

Next, set up a table to hold your survey responses:

1. Click the SQL Workshop tab.
2. Click SQL Scripts.
3. Click Upload.
4. Click Browse ; select the make_survey_responses.sql script and click Upload.
5. Click the make_survey_responses.sql icon.
6. Click Run , and then click Run on the Confirmation screen.
7. Optionally, view the results of the script execution, by clicking on the make_survey_responses.sql icon.

Step 2: Create the Application Shell

With the data in place, you can now assemble the application.

1. Click the Application Builder tab.
2. Click Create.
3. Accept the default Create Application option, and click Next.
4. Enter Workshop Survey as the name, and click Next.
5. Accept the Blank option as the Page Type, enter Oracle Application Express Workshop Survey as the Page Name, and click Add Page.
6. Enter two more blank pages with the Page Name Workshop Survey cont. and Thanks, respectively, and then click Next.
7. Select No Tabs , and click Next.
8. Click Next.
9. Because the survey will be anonymous, select No Authentication for Authentication Scheme, and click Next.
10. Select Theme 10 , and click Next.
11. Click Create.

Step 3: Add the Survey Questions into the Application

In the list of pages of your application, click the link for page 1 to go to its definition.

Create a region. From here you will add an HTML region into which you will place your first eight questions:

1. Click Create.
2. Select Region on this Page , and click Next.
3. Select HTML , and click Next.
4. Enter Oracle Application Express Workshop Survey in the Title field.
5. Select Page Template Body (1. Items below region Content) for Display Point, and click Next.
6. Enter the following text in the Region Source field, and click Next:

Please answer the questions below, using a scale of 1 to 5, where 1 is "poor" or "strongly disagree" and 5 is "excellent" or "strongly agree."

7. Click Create Region.

Add questions. Now add the questions into the region.

1. In the Items section of the page 1 definition screen, click the Add icon (the document icon with a "+" sign).
2. Click Create multiple items at the bottom of this screen.
3. In the Create Items in Region list, select the new HTML region—Oracle Application Express Workshop Survey.
4. Enter eight items named P1_Q1 through P1_Q8, and for the Type of each item, select Hidden.
5. Click Create Multiple Items.

Create a list of values. Now add a dynamic list of values to the application.

1. On the page definition screen for page 1, under Shared Components , in the List of Values section, click the Add icon.
2. For Create List of Values, select From Scratch and click Next.
3. Enter a value for Name, such as response options 1 through 5; select the Dynamic option; and click Next.
4. Enter the SQL query in Listing 2 into the page.

Code Listing 2: List of values query 

select 1 display_value, 1 return_value from dual union all
select 2 display_value, 2 return_value from dual union all
select 3 display_value, 3 return_value from dual union all
select 4 display_value, 4 return_value from dual union all
select 5 display_value, 5 return_value from dual
order by display_value

5. Click Create List of Values.

Add answers to the region. Now you need to add the answers to the region.

1. In the Items section of the page 1 definition screen, click the Add icon.
2. Click Create multiple items at the bottom of this screen.
3. In the Create Items in Region list, select the new HTML region: Oracle Application Express Workshop Survey ....
4. Enter eight items named P1_A1 through P1_A8.
5. Change the item Label field to the case-sensitive string &P1_Q n .—where n is the number of the current answer. (Note the period at the end of these values.)
6. For the type of each item, select Radio Group.
7. Click Create Multiple Items.
8. In the Items section of the page 1 definition screen, under Items, for each of the new Radio Group items, click the name— P1_A n ,—and in the Edit page, in the List of Values section, select the name you gave the list of values you created (such as response options 1 through 5 ), and enter 5 for Columns.

Add a process. Now add a process to populate the P1_Q n question items with the values of the first eight questions.

1. In the Processes section of the Page Processing section on the page 1 definition screen, click the Add icon.
2. Select PL/SQL, and click Next. 3. Enter a name.
4. For Point, select On Load-Before Header.
5. Click Next.
6. Copy and paste the following into the text box: 

for c1 in (select * 
             from survey_questions 
             where id < 9 order by id)
loop
    if c1.id = 1 then
        :P1_Q1 := c1.question;
    elsif c1.id = 2 then
        :P1_Q2 := c1.question;
    elsif c1.id = 3 then
        :P1_Q3 := c1.question;
    elsif c1.id = 4 then
        :P1_Q4 := c1.question;
    elsif c1.id = 5 then
        :P1_Q5 := c1.question;
    elsif c1.id = 6 then
        :P1_Q6 := c1.question;
    elsif c1.id = 7 then
        :P1_Q7 := c1.question;
    else
        :P1_Q8 := c1.question;
    end if;
end loop;

and click Next.
7. Click Next , and finally click Create Process.

Add validation. Now add validation to ensure that everyone who takes the survey answers all of the questions.

1. In the Validations section of the page 1 definition screen, click the Add icon.
2. Click Create Multiple, not null validations at the bottom of this screen.
3. Select the eight answer items ( P1_A1 through P1_A8 ), and click Create Not Null Validation.

Add a Next button. Add a button called Next to the HTML region.

1. In the Buttons section of the page 1 definition screen, click the Add icon.
2. Select the Oracle Application Express Workshop Survey region, and click Next.
3. Select the Create a button in a region position , and click Next.
4. Click the brown [Next] link to enter Next as the Name and Label , and click the Next button.
5. Click Next.
6. For Position, select Bottom of Region , and click Next.
7. For Branch to Page, click the pop-up-list icon, select 2 Workshop Survey cont. , and click Create Button.

Build Page 2

Now build page 2 to include the final two questions and their answers.

Create a region. Navigate to the definition screen for page 2, by entering 2 into the Page field and clicking on Go . On page 2, use the same instructions and region name you used to create the region in page 1.

Add questions to the region. Follow the instructions you used to create the questions on page 1. Name these page 2 questions P2_Q9 and P2_Q10 .

Add answers to the region. Follow most of the instructions you used to create the answers on page 1. Name these page 2 answers P2_A9 and P2_A10 , label the answers &P2_Q9 . and &P2_Q10. , and select Textarea for the Type attribute of these answers.

Add a PL/SQL process. Follow the instructions you used earlier to add a PL/SQL process to page 1 to add the following to a PL/SQL process in page 2: 

for c1 in ( select * 
              from survey_questions 
             where id >= 9 order by id)
loop
    if c1.id = 9 then
        :P2_Q9 := c1.question;
    else
        :P2_Q10 := c1.question;
    end if;
end loop;

Add another PL/SQL process. To store your results, add another process to this page. This will also be a PL/SQL process, but it will fire with a Point of On Submit-After computations and validations. Add the following statement to the process: 

insert into survey_responses values (null, 
:P1_A1, :P1_A2, :P1_A3, :P1_A4, :P1_A5, 
:P1_A6, :P1_A7, :P1_A8, :P2_A9, :P2_A10);

Build Page 3

To complete your survey application, add an HTML region to page 3 that says: 

We appreciate your taking the time to respond to our survey.

With all three application pages completed, click the Run icon to run the survey application.