Simulate variable arguments in PL/SQL

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.

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.