Re: Pass different # of parameters to a procedure?

From: Walter T Rejuney <BlueSax_at_Unforgetable.com>
Date: Fri, 13 Oct 2000 10:00:41 -0400
Message-ID: <39E71589.C76B19D1@Unforgetable.com>

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

One way that you could do this is to pass a user-defined record to the procedure. This user-defined record would have all of the fields that you need to use plus an additional field that would be a boolean that would indicate whether any given parameter is to be used or not. When you are getting ready to call the procedure you would populate the parameter fields with whatever values you want to pass and then set the appropriate boolean fields. Something like this:

set pages 0;
set serveroutput on;
set echo on;
set sqlnumber on;
Declare Type rec_type is record

             (field1 varchar2(80),
               bfield1 boolean,
               field2 number(5),
               bfield2 boolean,
               field3 date,
               bfield3 boolean);

    myrec rec_type;

    Procedure myproc(Inrec rec_type) is
    Begin

        if Inrec.bfield1 = true Then
            dbms_output.put_line('Field1: '||Inrec.field1);
        end if;
        if Inrec.bfield2 = true Then
            dbms_output.put_line('Field2: '||to_char(Inrec.field2));
        end if;
        if Inrec.bfield3 = true Then
            dbms_output.put_line('Field3:
'||to_char(Inrec.field3,'YYYYMMDD'));
        end if;

    End;
Begin

    dbms_output.enable(1000000);

     myrec.field1 := 'aaa';
     myrec.bfield1 := true;
     myrec.field2 := 5;
     myrec.bfield2 := false;
     myrec.field3 := sysdate;
     myrec.bfield3 := true;
     myproc(myrec);

End; Received on Fri Oct 13 2000 - 09:00:41 CDT