If you know lots of programming languages, then you’re
already accustomed to the ability to declare and use functions with a variable
number of arguments, where the number and type of arguments aren’t known at
design time. There have been different approaches (even in Oracle’s own
packages) that try to simulate this functionality by simply creating as many
defaulted arguments as possible. View
Listing A for an example.

This approach works for a while, but it becomes difficult
for both the maintainer of the function and the caller to handle this list of
arguments. I’ve seen other approaches that try to encode a list of arguments in
a single string and then parse the string on the inside of the function. Since
Oracle 8, there is a better way that developers often don’t consider: using a
simple VARRAY to wrap the variable portion:

create or replace type vargs as table of varchar2(32767);

/

show errors

There are some added benefits to using this method. For
instance, since it’s a collection; groups of function arguments can be stored
in database tables. The variable argument function can be declared to receive
this kind of VARRAY type, which, when called on the command, looks almost like a variable list of arguments:

create or replace function func(… argsvargs …) …

exec func(vargs('one','two','three','four'));

As a more complete example,
Listing B is a PL/SQL version of the classic C function sprintf,
implemented in pure PL/SQL. First, we need a helper package to clean up the
code and to implement each type of formatting (it’s not 100 percent C
compatible). Listing C shows
how I construct a function to implement sprintf from outside the package. Finally,
Listing D
contains the PL/SQL sprintf
in action. The code is simply for illustrative purposes and still needs work to make it work exactly like the ANSI C version
of sprintf from stdio.h.

TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!