PL/SQL Flexible Parameter Passing

With PL/SQL and mod_plsql you'll need to specify and IN parameter for each request parameter. The parameters you pass to a procedure can either be appended to the end of the URL, included in a POST (or PUT, etc.) request payload, or some combination thereof. Keep in mind that these IN parameters can have default values, so in order to successfully submit a request you don't have to specify them all. You just can't use more than you've spec'd for and you have include values for each parameter without a default, otherwise you'll hit a 404.

This limitation should never be much of an issue if you're dealing with static or a relatively small number of query parameters. But when dealing with web forms containing many parameters, your end-points can quickly become difficult to manage. Additionally, if you want to use the same procedure to handle a request in a variety of ways, this limitation could hinder your flexibility.

For instance, if one of your procedures was an API end point and you wanted to use it to to handle multiple HTTP verbs, you may not be interested in passing every parameter every time. But you don't need to pass every parameter, you have default values! OK, but if you're using the same procedure to handle a PATCH you might inadvertently overwrite unchanged values on the object you intend to modify with the API procedure. Even if you set the default value to NULL and treat NULL as an unchanged value, you have a conflict on your hands. What if that particular field is actually nullable? Wouldn't it then be prudent to actually treat NULL as an actual change to the object you're updating?

Fortunately, mod_plsql has a feature called flexible parameter passing which helps circumvent this issue and bring your PL/SQL web application more in-line with how request variables should be handled. Using flexible parameter passing allows us to determine exactly which parameters have been passed instead of relying on default values.

The following procedure is designed to work with flexible parameter passing. You shouldn't need to modify the default mod_plsql configuration to get this to work.

CREATE OR REPLACE PACKAGE PLSQL_DEMO AS
	PROCEDURE print_arguments (
		name_array in owa.vc_arr,
		value_array in owa.vc_arr
	);
END PLSQL_DEMO;
/

CREATE OR REPLACE PACKAGE BODY PLSQL_DEMO AS
	PROCEDURE print_arguments (
		name_array in owa.vc_arr,
		value_array in owa.vc_arr
	) IS
	BEGIN
		for n in 1..name_array.count loop
			htp.p(name_array(n) || ' : ' || value_array(n));
		end loop;
	END print_arguments;
END PLSQL_DEMO;
/

The print_arguments procedure will accept an array of names and an array of values. So how do we actually pass an owa.vc_arr into name_array and value_array? This is where mod_plsql automagic kicks in. Instead of accessing our package with:

http://example.com/pls/myapp/generic\_controller.print\_arguments

We now access it with:

http://example.com/pls/myapp/\!generic\_controller.print\_arguments.

Query parameters are passed in exactly the same way, so your HTML and scripts shouldn't need to be updated. The only thing that changes is how your PL/SQL handles those parameters. As an example, if the query string passed to this procedure were "?var1=5&var2=6", name_array would be ['var1', 'var2'] and value_array would be ['5', '6'].

Note that the values passed will be varchar2 types. This differs slightly than if you were to have a number or date IN parameter specification. In these cases PL/SQL automatically handles the conversion before the procedure begins. With flexible parameter passing you're responsible for parsing the values yourself.