PL/SQL: Procedure with variable number of parameters ?

[Frank Maesen]

Dear all,

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

Frank Maesen

[Dana Stockler]

As far as I know, it is not possible to have a variable number of
parameters in a PL/SQL procedure in the 'C' sense.

However, clever use of the default parameter mechanism can be
used to simulate multiple parameters.

-
Dana

Frank Maesen <fma...@luc.ac.be> wrote in article
<5hatpa$2eo...@news.belnet.be>...

[jin...@teleport.com]

On Wed, 26 Mar 97 10:28:58 GMT, fma...@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

[Graham Miller]

Hello,
There are two methods that i have used:

1) Procedure overloading.

2) Declare your parameters with default values.

I (almost) always use 'named parameter association' when calling
PL/SQL procedures (wherever i can).

Alas, when calling DB (packaged ) procedures from forms 4.5, all the
parameters have to be specified.

graham (aka grumpy)

fma...@luc.ac.be (Frank Maesen), thoughtfully produced:

>Dear all,

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

>Frank Maesen

Graham Miller ...
Opinions expressed are mine, they are free, and worth exactly what they cost.

[sui...@freenet.edmonton.ab.ca]

Hi Dana:

It has. You can try to define as record TYPE. It allows you to build
struct as C. It is handy too.

1. create a package.

2. In package specification, define the record TYPE you want.

3. Then, you can declare the TYPE variable reference to the TYPE you
defined in the package specification.

If you need example, please e-mail to me.

Best regards,

Dana Stockler (stoc...@ingdata.no) wrote:
: As far as I know, it is not possible to have a variable number of

: parameters in a PL/SQL procedure in the 'C' sense.

: However, clever use of the default parameter mechanism can be
: used to simulate multiple parameters.

: -
: Dana

: Frank Maesen <fma...@luc.ac.be> wrote in article
: <5hatpa$2eo...@news.belnet.be>...

: | Dear all,

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

: |

--
Best regards,