Wednesday, September 27, 2006

mod_plsql and flexible parameter passing

This is a continuation on a previous entry, where I introduced a HTML wrapper. This wrapper has a nice way to produce a combo box:
htp.p(htmlform.selecttable(label => 'Directory:'
, name => 'p_directory'
, tablename => 'dba_directories'
, textcol => 'directory_name'
, listsize => 10
, orderby => '1'
, p_default => r_sel_pd.directory_name ) );
This little piece of code will actually display a combo box, 10 lines deep, based on the dba_directories table. It will display the column directory_name, and pass it back in a parameter, called p_directory. The list will be ordered on the (one) column displayed.
As the select_table function is overloaded, the fact that listsize is mentioned means it will be a multi select combo box. For the HTML coders amongst you, the select element will have multiple="multiple" added.

This means the call to the database will pass an unknown number of parameters. how to handle this?
Well, this is where the flexible parameter passing of mod_plsql kicks in. First of all, we'll have to tell mod_plsql we're passing an unknown number of parameters. this is done by changing the call to the procedure that will handle the request. in stead of this:
HTP.p(htmlform.formstart(p_action => 'fablogspr.bsavepd'));
the call is changed to this:
HTP.p(htmlform.formstart(p_action => '!fablogspr.bsavepd'));
Note the exclamation mark. That's all there is to change the mode of mod_plsql.

Procedure changes.
The original procedure just had three parameters, id, profile and directory name. The declaration part looks like:
procedure bsavepd (
p_profiel IN VARCHAR2,
p_directory IN VARCHAR2 );
The new procedure declaration is:
procedure bsavepd(
name_array IN OWA.vc_arr,
value_array IN OWA.vc_arr)
As I designed the page to use three parameters originally, and the first two do not change, the combo box procedding can be:
for i in 3 .. name_array.LAST
savenewpd(l_prf, value_array(i));
end loop;
WHEN extra_page
-- Just return the generated error or warning page.
WHEN others
I do not need the name_array(i) in the update loop, as it will always contain the name of the parameter I defined in the htmlform.selecttable above, p_directory.
Enjoy modding!

No comments: