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; /
print_arguments procedure will accept an array of names and an array of values. So how do we actually pass an
value_array? This is where mod_plsql automagic kicks in. Instead of accessing our
We now access it with:
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
Note that the values passed will be
varchar2 types. This differs slightly than if you were to have a
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.