Re: Adding a New Parameter to Stored Procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 23 Dec 1998 17:00:00 GMT
Message-ID: <36832131.74994546@192.86.155.100>

A copy of this was sent to "Frank Siegel" <NorthernSnow_at_worldnet.att.net> (if that email address didn't require changing) On 23 Dec 1998 16:02:32 GMT, you wrote:

>Hello,
>
>I want to add a new (the 4th) Parameter to a Stored Procedure.
>"easy!"
>
>
>But we already have hundreds of calls to this proc and we
>dont want to update them all. Can we make the 4th parameter
>optional. If so, what is the syntax?
>
>Thank You!
>Frank S.
>
>

create or replace YOUR_PROCEDURE

( old_parm1 in varchar2, 
  old_parm2 in varchar2,
  old_parm3 in varchar2,
  NEW_PARM  in varchar2 DEFAULT 'some value or null' )

as
begin
  ...
end;

Default the new parameter and make sure it is the last parameter. Now a call like:

exec your_procedure( 'a', 'b', 'c' )

will still work. Default NEW_PARM to NULL or some hard coded value.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Dec 23 1998 - 11:00:00 CST