14-6. Creating a Web Form Dropdown List from a Database Query
Problem
Your web form requires a dropdown list whose elements are drawn from a database table.
Solution
Use the htp.formSelectOpen, htp.formSelectOption and htp.formSelectClose procedures in the PL/SQL Web Toolkit to generate the required HTML tags. For example, suppose you need to use the HR schema to create a dropdown list of job titles from the JOBS table. Here's how you'd do it.
create or replace procedure job_list as
cursor driver is
select job_id, job_title
from jobs
order by job_title;
begin
common.header ('Job Title'),
htp.formSelectOpen ('id', 'Job Title: '),
htp.formSelectOption ('', 'SELECTED'),
for rec in driver LOOP
htp.formSelectOption (rec.job_title, cattributes=>'VALUE="' || rec.job_id || '"'),
end LOOP;
htp.formSelectClose;
common.footer;
end job_list;
This procedure produces the following web page.
Figure 14-4. Dropdown list created by job_list procedure
How It Works
The htp.formSelectOpen procedure generates the HTML <SELECT NAME="id">, which defines the dropdown list in the web browser. In addition the procedure uses the second parameter as the prompt for the dropdown list. In this example the prompt is Job Title:.
The call to htp.formSelectOption procedure defines the elements of the dropdown list. The first parameter is the text displayed in the list and the second parameter preselects the element in the list when it is first displayed. In this example the first call to the htp.formSelectOption procedure defines the default selected element in the list to an empty value.
The subsequent calls to htp.formSelectOption that appear in the cursor for loop define the remaining elements in the dropdown list using the data selected from the JOBS table. The cattributes parameter is used to change the default value returned by the web browser when the element is selected from the list.
The call to htp.formSelectClose generates the </SELECT> HTML tag to close the dropdown list. Dropdown lists usually appear within the <FORM> tags to accept user input and process that input on a subsequent page.
Note See Recipe 14-3 for more information on creating an input form.