Re: Pass different # of parameters to a procedure?

steveee_ca_at_my-deja.com wrote:
>
> Hi Everyone,
>
> I'm confronted with a situation where we're wanting to create a stored
> procedure that will accept different numbers of IN parameters.
> Specifically, it could execute with, say 2 parameters supplied or 5
> parameters supplied to it.
>
> We've considered overloading a package, and that remains an option, but
> there are quite a few possible combinations of parameters that the
> procedure could receive.
> It is supposed to take the parameters it gets and then update a table,
> changing the fields in a table for which it has received a parameter
> while leaving all others at their original value. Because of this
> requirement, simply providing a default value won't work because we
> don't want the update statement to change column values to their
> default if they already had a value prior to the update.
>
> Sheesh! Normally I'm pretty good with words but I'm failing miserably
> trying to explain this.
> As far as I know my only choices are:
> 1. Supply a value to every IN parameter
> 2. Supply values to some parameters and let the others use a default
> value.
> 3. Overload the procedure in a package.
>
> Any help on this?
> Thanks in advance for ideas..its version 8.1.
> S
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

A couple of options...

1. if you have an upper bound, then have default values assigned to those parameters that are not passed.
2. if you don't know an upper bound, pass a single parameter which is an array of values using a plsql table.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk
(faster/mirrored at http://www.oradba.freeserve.co.uk)

Its not the voices in my head that bother me... 
  its the voices in yours.