Variable number of arguments in PL/SQL stored procedure

Question

Can a procedure PL/SQL take a variable number of arguments?

In my case, the procedure is called by the submit button of a form, and the form has variable number of inputs.

Solution 1

You don't mention it, but are you using mod_plsql?

If so, you should read about flexible parameter passing.

In short, prefix your procedure name with an exclamation mark in your browser and define your procedure with a name_array and value_array.

Solution 2

Sort of. You can give the procedure parameter default values:

CREATE PROCEDURE myproc( p_value_a NUMBER DEFAULT 1, 
                         p_value_b NUMBER DEFAULT 2 ) AS
    ...

which you could call like this:

myproc( 999 );

or like this:

myproc (p_value_b => 11 );

Comments

[Codo]

I'd rather use NULL as the default value. But otherwise, the answer is good.

[Moltes]

I thought about default values, but the number of arguments can be very big. A procedure with 100 arguments with a null default value, strange no ?

[eaolson]

I've never seen a procedure that needs 100 arguments. If you have 100 form variables, you need to do some server-side processing before sending it to your database.

[Moltes]

I searched for flexible parameter passing like you said, and i found this : asktom.oracle.com/pls/asktom/… Problem solved ! Thank you very much

[Martin Schapendonk]

Thanks for accepting. The text in my answer is also a link and it would have saved you from searching again yourself :-)