Re: PL/SQL: Procedure with variable number of parameters ?

From: <jingram_at_teleport.com>
Date: 1997/03/28
Message-ID: <333ba55a.30474540@news.teleport.com>#1/1

On Wed, 26 Mar 97 10:28:58 GMT, fmaesen_at_luc.ac.be (Frank Maesen) wrote:

>Can anybody explain how to create a procedure in PL/SQL which can accept
>a variable number of parameters.

That depends on what you need to do. It's certainly feasible to have a procedure that accepts a parameter of type varchar2 that is a delimited string. If your parameters were always passed in the same order and NULL values included in the string, you could always do something like this:

PROCEDURE Delimited_Parameters (vParameterList IN varchar2)

IS

   iDelimiterPos         integer := 0;
   vParameter1         varchar2 (20);
   vParameter2         varchar2 (20);
   vParameter3         varchar2 (20);

.
.

   vParameterN         varchar2 (20);
   vParameters          varchar2 (2000);

   vDELIMITER       CONSTANT char (1) := '^';


   PROCEDURE NextWord 
         (vStringToParse IN OUT varchar2,
          vReturnWord        OUT varchar2)

   IS

      iDelimiterPos integer := 0;

   BEGIN

      iDelimiterPos := instr (vStringToParse, vDELIMTER);
      vReturnWord := substr (vStringToParse, 1, iDelimiterPos);
      vStringToParse := substr (vStringToParse, 
                                              (iDelimiterPos + 1));

   END NextWord;

BEGIN
   NextWord (vStringToParse => vParameters,

                     vReturnWord    => vParameter1);
.

.
.

   NextWord (vStringToParse => vParameters,

                     vReturnWord    => vParameterN);

END Delimited_Parameters;

However, you still have to explicityl declare a maximum number of parameters for the procedure and each parameter would have to be extracted from the string individually. PL/SQL doesn't allow you to create variables that haven't been defined, so there's no way around this.

If this isn't what you're trying to do, you might take a look at overloading. This requires you to write multiple pieces of code using the same name, but the modules all have different numbers and/or types of parameters.

Jonathan Ingram
Meridian Technology Group
503.639.0816 Received on Fri Mar 28 1997 - 00:00:00 CST