Developer

Handling HTML multi-selects in PL/SQL


Dealing with HTML multi-selects in Oracle's PL/SQL is a bit trickier than I'd like. Depending on how many items the user picks out of the multi-select, you either get back a simple VARCHAR2 object or a special array object. This means you have to write two separate methods to deal with the two cases.

Let's say you've got an HTML form that contains a multi-select like this:

<select multiple name="p_org_id" id="p_org_id" size="3">

<option value="AV001">Amerigo Vespucci & Sons</option>

<option value="AV002">Antarctic Ventures, Inc.</option>

<option value="AV003">Alamo Vectronix Ltd.</option>

</select>

Let's assume your form is a GET and not a POST. If a user picks one of these items, what gets appended onto the query string will be something like this:

?p_org_id=AV001

If the user selects a couple of items, the query string will look something like this:

?p_org_id=AV001&p_org_id=AV002

In the first instance, PL/SQL will be looking for a method that handles a VARCHAR2 called p_org_id. I make the p_org_id variable default to null in case the user does not select anything from that multi-select. The spec for this method would be something like this:

procedure process_form(p_org_id VARCHAR2 default null);

In the second instance, you get back a data structure called ident_arr, which is part of the OWA_UTIL package in PL/SQL. One of the nice things here is that you don't have to worry about the null bit; the only way this version of the method would be called is if the user had selected multiple items from your form. The method signature would look something like this:

procedure process_form(p_org_id owa_util.ident_arr);

All that's left is figuring out how to process that data structure. Luckily, it's like the name sounds — just an array you can loop thru.

htp.p('You selected: ');

FOR i IN 1..p_org_id.count LOOP

htp.p('<br/>' || p_org_id(i));

END LOOP;

I'm still searching for a way to handle this in a single method but, so far, this is the only way I've found to handle all the possible conditions coming from a multi-select. Two separate methods seems to be the minimum set required. In theory, you could have a third method to handle the null case but, as I did above, I usually combine that with the code handling the VARCHAR2 case.

Editor's Picks