Re: PL/SQL variable argument list function

From: DanHW <danhw_at_aol.com>
Date: 2 Jul 1998 01:23:55 GMT
Message-ID: <1998070201235500.VAA20522@ladder03.news.aol.com>

>Is it possible to create a function/procedure in PL/SQL with a
>variable number of arguments (as in C)?
>
>If the decode() function was written in standard PL/SQL, then it
>should be possible, right?
>
>I don't see any reference to this in the Oracle documentation.
>
>Thanks for any help,
>
>
>Nuno Guerreiro
>
>

Yes - there are two ways, depending on exactly what you are trying to do.

Example 1:

procedure my_proc (arg1 integer,

     arg2 integer default 1,
     arg3 varchar2 default 'X')

...
Since default values are provided, you can do this:

my_proc(4);

and the default values wil be used. Likewise you can do this:

my_proc (5,4);

and the default value will be used for the third argument.

If you have a big list, and defaults provided (which can be null, incidently), you can also do this, by explictly specifying the arguments

my_proc(arg3='A', arg1=3);

Now only arg2 will use the default value.

The second option involves overloading the procedure. To do this, you have functions/procedures with the same name, but the arguments for each function are different in data type and order.

For example, you can define
 my_proc(x integer) and
my_proc (x varchar2)

If the argument passed in is an integer, the first will be used; if it is a varchar2, the second one will be used.

Hope this wasn't too confusing...

Dan Hekimian-Williams Received on Wed Jul 01 1998 - 20:23:55 CDT