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.
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.
Step 2: Create the Application Shell
With the data in place, you can now assemble the application.
1. Click the Application Builder tab.
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.
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).
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.
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.
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.
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.
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.
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.
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.
Raj Mattamal (firstname.lastname@example.org) is a principal product manager in Oracle's Server Technologies division. He has been with Oracle since 1995.