17.5 How do I…Use HTML tables in my web documents?
Problem
While designing forms in my web applications, I need to include multiple checkboxes or radio buttons that have the same name. When the form is posted, values of form fields are received as variables by a stored procedure. How do I use multi-valued form fields?
Technique
Irrespective of whether you use the GET or POST method to pass parameters from an HTML form, you do not need to be concerned how the PL/SQL Agent receives parameters unless you are using form fields that can be multi-valued. A multi-valued form field is a field that occurs several times in the form and has the same field name but different values.
You can also set fields of different types to be multi-valued if they bear the same field name. Typically, checkboxes and radio buttons are the multi-valued fields, but you can very well have other fields be multi-valued as well. For instance, a series of hidden form fields with the same name, or an HTML SELECT list with the SIZE parameter greater than 1, enables the user to select multiple values for the same form field by using the CTRL key while clicking multiple items in the SELECT list. Special care also has to be taken while passing multiple values for form fields as a parameter to the stored procedure that processes the submitted form. A PL/SQL table is used to create an array of values for the multi-valued form field when the form is submitted.
Steps
1. Run SQL*Plus and connect as the WAITE account. If you have not created the DEPT17 table from How-To 17.2, then run CHP17_1.SQL, shown in Figure 17.19, which creates the DEPT17 table used in this How-To.
SQL> START CHP17_1
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
2. Run the CHP17_8.SQL file in SQL*Plus, as shown in Figure 17.20. The procedure contained in the file queries the DEPT17 table created in Step 1 and displays the records as a multi-valued checkbox form field.
Line 1 specifies the keywords required to create the stored procedure and names it. Lines 2 through 4 define a cursor to fetch all records from the DEPT17 table. The HTP.HTITLE procedure in line 7 generates the header and title tags. Lines 6 and 19 begin and end the HTML document with the HTP.HTMLOPEN and HTP.HTMLCLOSE procedures, and lines 8 and 18 begin and end the body of the HTML document with the HTP.BODYOPEN and HTP.BODYCLOSE procedures. Line 9 begins a form with the HTP.FORMOPEN procedure. The parameter passed to the HTP.FORMOPEN procedure specifies the name of the procedure receiving the form data when the form is submitted.
Note the use of the HTP.FORMHIDDEN procedure in line 10 that creates a hidden form field named DEPTS with a value of DUMMY. Hidden form fields are usually used to pass state information from one form to another.
As we would be using a PL/SQL table to pass the values of the multi-valued form field to a stored procedure, and a PL/SQL table cannot default to NULL, it is necessary that we create the first element with a dummy value.
The FOR loop on lines 11 to 15 creates a checkbox for each department in the DEPT17 table. The HTP.FORMCHECKBOX procedure is used to create a checkbox. The value for the checkbox is the DEPT_NAME column value. All checkboxes have the same DEPTS, each with a different value. The HTP.FORMSUBMIT procedure in line 16 creates a submit button that processes the form when selected. The HTP.FORMCLOSE procedure in line 17 ends the form. The next step creates the stored procedure to process the form.
3. Run the CHP17_9.SQL file in SQL*Plus, as shown in Figure 17.21. The procedure within the file processes the form created in the previous step and displays the departments for checked checkboxes.
In line 2, the only parameter to the procedure is of type OWA_UTIL.IDENT_ARR, which is defined as a PL/SQL table of VARCHAR2(30) in the OWA_UTIL package. The DEPTS parameter to the stored procedure represents the list of values for the multi-valued checkbox form field. As there is no guarantee that at least one checkbox will be checked when the form is submitted, a dummy string is included as the first value. Line 8 starts the counter at 2 because we had put in a dummy hidden field in the form. For PL/SQL tables, you can just loop through the values until the NO_DATA_FOUND exception is hit. This is exactly what is done in the loop in lines 9 through 13. The values for checkboxes that were checked are passed to the stored procedure using the PL/SQL table, and each value is printed to the web document in line 10. The NO_DATA_FOUND exception is raised when there are no more elements in the PL/SQL table. The exception handler in lines 15 to 17 handler merely prints the </BODY> and </HTML> tags to the web document before exiting.
4. Run a browser and open the URL http://localhost/owa_waite/plsql/ dept_frm17. Figure 17.22 shows the form displayed by the Netscape browser.
A checkbox is displayed before the name of each department. Check a few checkboxes and press the Submit button. Each of the values for checked checkboxes from the calling form are displayed. Figure 17.23 shows the output in the Navigator window after the form is posted.
How It Works
PL/SQL tables are used to pass values of a multi-valued form field as an array of values to the processing stored procedure. The PL/SQL Agent can only pass parameters to PL/SQL tables, which have a base type of VARCHAR2. For other datatypes, the values can then be explicitly converted using conversion functions.
Step 1 creates a sample table and data. Step 2 creates a stored procedure that generates a form with a multi-valued checkbox form field with values from the sample table. When the form is submitted, the stored procedure created in Step 3 receives the values of checked checkboxes and prints those values to the browser. Step 4 shows the operation of the simple form within a browser.
Comments
A PL/SQL table is used as a parameter to the processing procedure to handle a multi-valued form field. Refer How-To 10.9 for more information on PL/SQL tables.