Home » SQL & PL/SQL » SQL & PL/SQL » Optional parameters in oracle procs
Optional parameters in oracle procs [message #38326] Wed, 10 April 2002 14:38 Go to next message
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 Go to previous messageGo to next message
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);
Re: Optional parameters in oracle procs [message #38329 is a reply to message #38327] Wed, 10 April 2002 15:51 Go to previous message
Manish
Messages: 79
Registered: December 2000
Member
hi,
it works fine.
thanks a lot for the help
Regds
Manish
Previous Topic: DDL Trigger not firing -- Why??
Next Topic: How to obtain the owner and name of a calling program
Goto Forum:
  


Current Time: Tue Sep 16 14:59:22 CDT 2014

Total time taken to generate the page: 0.11395 seconds