Re: Pl/sql variable parameter list

Procedures defined inside of an Oracle PACKAGE (body) have a wonderful characteristic. Notice:

create package example is

procedure proc1(s in varchar);
procedure proc1(n in number);
procedure proc1(s in varchar, n in number);

Notice that these three procedures ALL HAVE THE SAME NAME; Oracle would determine, based upon the type and number of parameters as to which procedure would actually be called.

Each may have different code, for example:

create package body example is
procedure proc1(s in varchar) is
begin

   if length(s) > 5 then

      do_something;
  end if;
end;
procedure proc1(n in number) is
begin
  if n > 5 then
    do_something_else;
  end if;
end;
procedure proc1(s in varchar, n in number) is begin
  if length(s) > 5 AND n > 5 then
    do_nothing;
  end if;
end;

You might want to read up on this way cool overriding of parameters.

Doug Cowles <dcowles_at_nospambigfoot.com> wrote in message news:38309400.E599A0DF_at_nospambigfoot.com...
> Can someone cut be the basics on passing in a variable list of
> parameters
> to a PL/SQL procedure or function (both possible?) - A rather
> irritating
> task of populating a many to many association table from a matrix has
> befallen me. There are 50 entries in one table (businessfunction) and
> 15
> entries in the other (groups), and the many to many table could contain
> up
> to 50*15 = 750 rows, but actually less than that. If it was a simple
> iterative
> loop, it would be simple. What I'm envisioning is executing a procedure
>
> 50 times by hand, with the various entries for the other table listed
> off in
> the parameter list like
> exec populate ('businessfunction1',g1,g5,g8,g9,g10)
> or
> exec populate ('businessufnction2',g1,g2,g3,g4,g5,g6,g7,g8,g9,g10)
> Then I can code the insert statements into the procedure or function.
> Can anyone give me the basics on how to work with a variable list
> like this?
>
> Thanks,
> Dc.
>
Received on Wed Nov 17 1999 - 18:22:36 CST