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

5

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;
/
Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

5 Comments

  1. This is good to know, I also found parameter passing a problem when form processing and wrote similar functions to deal with the problem. I did however discover another problem that I have not been able to find a resolution to. I need to be able to distinguish between form data and URL parameters (GET/POST) when passing form data to a function. I use portal.wwpro_api_parameters.retrieve(owaVA_ARR_names, owaVC_ARR_values); I opened a TAR with Oracle but was unable to get a resolution to the problem. All they did was question my need for such a thing. My reason is simple… I need to distinguish the difference. So, looks like I need to find a “new trick”.

  2. Pingback: glamour-agency

  3. Luis Cuellar on

    The original article is available through the Internet Archive for those that might want to read it.

    [is the answer to the spam protection supposed to be a “number” or a “string”?]

  4. we have had a great deal of success with a controller developed for a large system with high security requirements.
    one pl/sql facade procedure is all that is web facing and uses a small meta data repository to enforce authentication/access and data validation.

    the trick to make things more manageable is to devise a numeric format for the description of all web forms and data items. as security was paramount, data validation was pushed out from the business logic to the controller. the controller enforces system wide and data value specific data rules by associating these values with meta data rules that use regular expressions for validation. this direction wards off a slew of web application attack methods.

    developers loved the fact that data validation was performed for their interface procedures simply by associating form fields with these rules and that by calling a specific api they could also populate their web forms with client side java script that used the same rules.

    the controller api has a bunch of system services built in for debugging, error handling etc…

    for more info visit planetxsolutions.com

  5. Hi,

    We got a PL/SQL framework emulating what the original Struts (from Apache Jakarta) is doing.

    We use mod_plsql to always call the same package that use a struts-config.xml file to get the informations required to dispatch the process to the appropriate PL/SQL procedure. We also use Stamps (known previously as HTT, http://stamps.sourceforge.net/) as a tag replacement framework based on HTML templates. All the Stamps tags emulate Struts-JSP tags for form fields, links , actions, etc…

    That framework was developed as an open source projet for a Quebec government agency.

    If more informations are needed, don’t hesitate to contact me.