PL/SQL and HTML Form Checkboxes

Because PL/SQL does not have the same support for arrays that other programming languages have, handling checkboxes in HTML forms can be a bit trickier than you might expect. An example on how to handle checkboxes is available on Oracle's documentation on Developing Applications with the PL/SQL Web Toolkit, but this example doesn't show the whole picture.

If you have a series of checkboxes in your HTML form and pass these to a stored procedure, that procedure may receive one of three possible types of value: a null value, a single value or multiple values. If it receives a single value (only one checkbox was checked), it will be a VARCHAR2 value. If it receives multiple values (more than one checkbox was checked), it will receive a parameter of type TABLE OF VARCHAR2. In order to handle multiple values, you must either declare a TABLE OF VARCHAR2 type or else use a predefined type, such as OWA_UTIL.IDENT_ARR.

The documentation mentioned above shows briefly how to loop through multiple values using OWA_UTIL.IDENT_ARR. However, it does not explain how to declare your own TABLE OF VARCHAR2 type and use that method. Also, if you follow the example in the documentation, you will find that it only works if you select more than one checkbox. To ensure that you can handle null and single values, you need to overload the procedure to accept the checkbox parameter as a single VARCHAR2 type also. The example below is a more complete example.

CREATE OR REPLACE PACKAGE pkg_checkboxes IS

    TYPE chkArray IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
    PROCEDURE display_form;
    PROCEDURE process_form(checkboxes IN VARCHAR2 DEFAULT NULL);
    PROCEDURE process_form(checkboxes IN chkArray);

END pkg_checkboxes;

CREATE OR REPLACE PACKAGE BODY pkg_checkboxes IS

    PROCEDURE display_form IS
    BEGIN
        htp.p('<html>');
        htp.p('<head>');
        htp.p('<title>Checkboxes Testing</title>');
        htp.p('</head>');
        htp.p('<body>');
        htp.p('<form name="frmChk" method="post" action="cp_cpw_checkboxes.process_form">');
        htp.p('<input type="checkbox" name="checkboxes" value="Japan" /> Japan<br />');
        htp.p('<input type="checkbox" name="checkboxes" value="China" /> China<br />');
        htp.p('<input type="checkbox" name="checkboxes" value="Russia" /> Russia<br />');
        htp.p('<input type="checkbox" name="checkboxes" value="USA" /> USA<br />');
        htp.p('<input type="checkbox" name="checkboxes" value="Canada" /> Canada<br />');
        htp.p('<br />');
        htp.p('<input type="submit" value="Submit" />');
        htp.p('</form>');
        htp.p('</body>');
        htp.p('</html>');
    END display_form;

    PROCEDURE process_form(checkboxes IN VARCHAR2 DEFAULT NULL) IS
    BEGIN
        htp.p('<p>You selected:</p>');
        htp.p('<ul>');
        IF checkboxes IS NOT NULL THEN
            htp.p('<li>'||checkboxes||'</li>');
        ELSE
            htp.p('<li>No checkbox selected</li>');
        END IF;
        htp.p('</ul>');
    END process_form;

    PROCEDURE process_form(checkboxes IN chkArray) IS
    BEGIN
        htp.p('<p>You selected:</p>');
        htp.p('<ul>');
        FOR i IN 1..checkboxes.count LOOP
            htp.p('<li>'||checkboxes(i)||'</li>');
        END LOOP;
        htp.p('</ul>');
    END process_form;

END pkg_checkboxes;

If you first look at the package specification, you will see that I have declare a TABLE OF VARCHAR2 type called chkArray. We will use this type when looping through the checkboxes that have been submitted later. Alternatively, you can leave this out and use the OWA_UTIL.IDENT_ARR type as explained in Oracle's documentation. I prefer to declare my own type, but the choice is yours.

Moving onto the package body, you will find the display_form procedure, which basically prints the HTML form we will be using for this example. It simply prints five checkboxes with basic descriptions on the page, along with a submit button. You will probably notice that there are two process_form procedures. The reason for this is that in order to handle each of the possible situations when the checkboxes are submitted, we need to overload the procedure to handle both VARCHAR2 values (which will take care of null values also), and our chkArray type, which takes care of multiple values.

In the first process_form procedure, we simply check whether the parameter checkboxes is null. If it's not, we print the value of the single checkbox that was submitted. If it is we print a message explaining that no checkbox was selected. In the second procedure, we loop through the checkboxes parameter, and print the value of the multiple checkboxes that were submitted as list items.