Optional parameters in oracle procs [message #38326] |
Wed, 10 April 2002 14:38 |
Manish
Messages: 79 Registered: December 2000
|
Member |
|
|
Hi,
i guess we can have optional paramters in an oracle proc. But what exactly is the syntax for achieving that?
like suppose i need an proc which has abt 8 parameters and 3 of which are optional then how do i define the procedure and handle the optional parameters?
and do i need to have these optional parameters in the end of the parameter list. Cause if i these parameters interspersed between regular parameters then skipping it will create a problem i guess!
any pointers will behelpful
Thanks
Manish
|
|
|
Re: Optional parameters in oracle procs [message #38327 is a reply to message #38326] |
Wed, 10 April 2002 15:05 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
If you want to use positional parameters (list them in order on the call), then you will have to put the parameters with defaults at the end, and here is the default syntax:
create or replace procedure foo
(p1 in number, p2 in number default 1)...
or you can use:
create or replace procedure foo
(p1 in number, p2 in number := 1)...
If you want to intersperse those parameters with defaults among those without defaults, then you will need to use named parameters on the call:
create or replace procedure foo
(p1 in number, p2 in number default 1, p3 in number)...
sql>exec foo(p1 => 10, p3 => 30);
|
|
|
|