Trick for multiple and flexible parameter passing with MOD_PLSQL and Web PL/SQL toolkit

This tip comes from a very interesting paper by John Flack. It is titled MVC Development in PL/SQL and appeared in the August issue of the ODTUG Technical Journal Online. However, it is unfortunately only accessible to ODTUG members. I write his tip down because two months ago, I was doing some extensions on top of the Repository Object Browser (ROB), part of Oracle Designer, and I was looking for exactly this. Erwin Groenendal could vaguely remember that such a thing was possible but he had no details right then and there (although later he sent me an email on it). I also browsed through some of the Web related books in the Oracle Bookstore at ODTUG but to no avail. So to make sure, I never forget again – and others who may be looking for the same information – here it is, courtesy of John:

Fortunately, there is an alternative to passing data from a form to an action procedure in one parameter per form field. By identifying the procedure in the URL with a leading exclamation point, you can tell mod_plsql, the PL/SQL module that provides the interface between the web server and stored procedures, to use flexible parameter passing. So, the URL becomes “http://www.mycompany.com/pls/mydad/!myprocedure�. With flexible parameter passing, the action procedure receives two arrays (PL/SQL “index by binary_integer� tables) of parameters instead of individual parameters. The first parameter array, name_array, contains a list of parameter names, which are matched one-to-one with the second array, value_array, containing the value of each parameter.

To make this even easier, I coded a utility package for accessing items in name and value array pairs. There are two overloaded get_value functions to get the value of a parameter by name. Since there can be more than one parameter with the same name, one version of the function lets you index which parameter to get and raises NO_DATA_FOUND if that parameter doesn’t exist. The other version just gets the first instance or returns a null if not found. There are procedures in the package for adding new parameters to a pair of name and value arrays, for removing one name and value pair, or for removing all of the instances of a parameter.

Any procedure called by MOD_PLSQL will have the following specification:
procedure myProcedure( name_array in owa.vc_arr, value_array in owa.vc_arr)

It can make use of the utl_parameter package to get to the parameters and their values.

CREATE OR REPLACE PACKAGE BODY utl_parameter IS
/* Return the index associated with the named parameter, starting from a given point. Function returns  NULL if no parameter with the name exists.
*/
FUNCTION get_index (
name_arr IN owa.vc_arr,
value_arr IN owa.vc_arr,
p_name IN VARCHAR2,
p_start IN PLS_INTEGER DEFAULT 0
) RETURN VARCHAR2 IS
cur_param PLS_INTEGER;
BEGIN
IF p_start > 0 THEN
cur_param := name_arr.NEXT(p_start);
ELSE
cur_param := name_arr.FIRST;
END IF;
WHILE cur_param IS NOT NULL LOOP
EXIT WHEN name_arr(cur_param) = p_name;
cur_param := name_arr.NEXT(cur_param);
END LOOP;
RETURN cur_param;
END get_index;
/* Return the value associated with the named parameter. Since more than one parameters are permitted
 to have the same name, there is an extra parameter to this function to allow caller to specify which occurrence of the parameter to use. Raises NO_DATA_FOUND when no parameter by this name is found with the specified occurrence number. An overloaded version defaults to the first occurrence and returns NULL if no parameter with the name exists.
*/
FUNCTION get_value (
name_arr IN owa.vc_arr,
value_arr IN owa.vc_arr,
p_name IN VARCHAR2,
p_occurrence IN INTEGER
) RETURN VARCHAR2 IS
cur_param PLS_INTEGER := 0;
BEGIN
FOR i IN 1..p_occurrence LOOP
cur_param := get_index(name_arr, value_arr, p_name, cur_param);
END LOOP;
IF cur_param IS NOT NULL THEN
RETURN value_arr(cur_param);
ELSE
RAISE NO_DATA_FOUND;
END IF;
END get_value;

FUNCTION get_value (
name_arr IN owa.vc_arr,
value_arr IN owa.vc_arr,
p_name IN VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
RETURN get_value(name_arr, value_arr, p_name, 1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END get_value;
/* Set the value of occurrence of a parameter. Raises NO_DATA_FOUND when no parameter by this name is found with the specified occurrence number.
*/
PROCEDURE set_value (
name_arr IN OUT NOCOPY owa.vc_arr,
value_arr IN OUT NOCOPY owa.vc_arr,
p_name IN VARCHAR2,
p_occurrence IN INTEGER DEFAULT 1,
p_value IN VARCHAR2
) IS
cur_param PLS_INTEGER := 0;
BEGIN
FOR i IN 1..p_occurrence LOOP
cur_param := get_index(name_arr, value_arr, p_name, cur_param);
END LOOP;
IF cur_param IS NOT NULL THEN
value_arr(cur_param) := p_value;
ELSE
RAISE NO_DATA_FOUND;
END IF;
END;
/* Delete an occurrence of a parameter. */
PROCEDURE remove(
name_arr IN OUT NOCOPY owa.vc_arr,
value_arr IN OUT NOCOPY owa.vc_arr,
p_name IN VARCHAR2,
p_occurrence IN INTEGER DEFAULT 1
) IS
cur_param PLS_INTEGER := 0;
BEGIN
FOR i IN 1..p_occurrence LOOP
cur_param := get_index(name_arr, value_arr, p_name, cur_param);
END LOOP;
IF cur_param IS NOT NULL THEN
name_arr.DELETE(cur_param);
value_arr.DELETE(cur_param);
END IF;
END remove;
/* Delete all occurrences of a parameter. */
PROCEDURE removeall(
name_arr IN OUT NOCOPY owa.vc_arr,
value_arr IN OUT NOCOPY owa.vc_arr,
p_name IN VARCHAR2
) IS
cur_param PLS_INTEGER := 0;
BEGIN
cur_param := get_index(name_arr, value_arr, p_name, 0);
WHILE cur_param IS NOT NULL LOOP
name_arr.DELETE(cur_param);
value_arr.DELETE(cur_param);
cur_param := get_index(name_arr, value_arr, p_name, cur_param);
END LOOP;
END removeall;

/* Add a new parameter. */
PROCEDURE add(
name_arr IN OUT NOCOPY owa.vc_arr,
value_arr IN OUT NOCOPY owa.vc_arr,
p_name IN VARCHAR2,
p_val IN VARCHAR2
) IS
next_param PLS_INTEGER;
BEGIN
next_param := nvl(name_arr.LAST,0) + 1;
name_arr(next_param) := p_name;
value_arr(next_param) := p_val;
END add;
/* Add a new parameter. Alternative version takes a single argument in the  form, param_name=value.
*/
PROCEDURE add(
name_arr IN OUT NOCOPY owa.vc_arr,
value_arr IN OUT NOCOPY owa.vc_arr,
p_setting IN VARCHAR2
) IS
v_name VARCHAR2(30);
v_val VARCHAR2(32000);
BEGIN
v_name := substr(list.head(p_setting,'='),1,30);
v_val := list.tail(p_setting,'=');
add(name_arr,value_arr,v_name,v_val);
END add;
END utl_parameter;
/