Software Development

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.

3 comments
psinger1
psinger1

APEX is probably the best way to go, but if want to go there, the old fashioned way to do it would be to use the REPLACE function to replace all the '&' to ',' and then call DBMS_UTILITY.COMMA_TO_TABLE to put them all into what I still call a PL/SQL table.

marv732
marv732

Soon after I first started writing web applications using Oracle PL/SQL packages I realised that I was continually rewriting the same code over and over, so, I and a couple of my co-workers wrote what we called our WAF (Web Application Framework) package (inspired by a book on OWAS3) that contained the repetitive parts of the code (e.g. headers, footers, standard processing) and overloaded the functions/procedures in it where required. This package was placed into a separate schema and we granted execute privilege to PUBLIC on it so anyone could call the appropriate part (with parameters) and get a consistent result. Part of this package were the following functions to allow for pre/post usage of the Spyglass or Apache web servers that Oracle used early on: FUNCTION GetFormValue (p_name in varchar2, p_num_entries in number, p_name_array in owa.vc_arr, p_value_array in owa.vc_arr ) return varchar2 is -- This version for OWS and OAS calling convention -- use the other function (without p_num_entries) for iAS (Apache) begin -- find the matching value for p_name (case insensitive search) for counter in 1..p_num_entries loop if upper(p_name) = upper(p_name_array(counter)) then return trim(p_value_array(counter)); end if; end loop; -- no match found return null; end; -- //////////////////////////////////////////////////////////// -- //////////////////////////////////////////////////////////// FUNCTION GetFormValue (p_name in varchar2, p_name_array in owa.vc_arr, p_value_array in owa.vc_arr ) return varchar2 is -- Apache version for iAS begin -- find the matching value for p_name (case insensitive search) for counter in 1..p_name_array.COUNT loop if upper(p_name) = upper(p_name_array(counter)) then return trim(p_value_array(counter)); end if; end loop; -- no match found return null; end; -- //////////////////////////////////////////////////////////// This could then be called to process a page as follows: v_state varchar2(1) := waf.GetFormValue('p_state', name_array, value_array); v_subnet_id addresses.NETWK_SUBNET_ID%TYPE := waf.GetFormValue('p_subnet_id', name_array, value_array); You could do something similar to avoid having to "reinvent the wheel" every time you need to process a page. These days I use Application Express which handles that and most other things for me - and it's free and supported with a ton of sample applications and tutorials. Greg

kayhan.yuksel
kayhan.yuksel

Hi Marv, I have tried APEX but unfortunately I couldnt customize theme of forms ( I don't want picture buttons or any pictures at all because ORA PORTAL web clipping couldn't handle pictures ) , I couldn't find any document about this either. How do you handle this? Best, Kayhan

Editor's Picks