Re: Adding a New Parameter to Stored Procedure

Peter Schneider wrote in message
<368160f1.22182137_at_personalnews.de.uu.net>... tkyte_at_us.oracle.com (Thomas Kyte) wrote:

>A copy of this was sent to "Frank Siegel" <NorthernSnow_at_worldnet.att.net>
>On 23 Dec 1998 16:02:32 GMT, you wrote:
>
>>Hello,
>>
>>I want to add a new (the 4th) Parameter to a Stored Procedure.

[...]

>>But we already have hundreds of calls to this proc and we
>>dont want to update them all.

[...]

>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,

generally this used to work fine; however in conjunction with Forms 5.0 this gives a problem: everytime you modify a procedure or a package spec, you must also recompile your Forms5 modules that call this procedure or packaged procedure; otherwise Forms throws an exception 'ORA-4062: timestamp of <your_procedure> has been changed'. AFAIK, this is an open bug in Forms 5; it does not affect any stored PL/SQL that calls your procedure with the modified parameter list (which will be valid again after recompilation).

Merry Xmas and a great new year,
Peter

--
Peter Schneider
pschneider_at_knuut.de

The same problem existes with forms 4.5 . A solution to this problem might be with procedure overloading . There is no need to recompile all the source code that uses this package if you add the new procedures at the end of the package . Only the package spec is visible to the runing program . So at the end of the package add a new procedure with the same name like

PACKAGE my_pack AS
  PROCEDURE old_procedure ( old_parm1 in varchar2,

                             old_parm2 in varchar2,
                             old_parm3 in varchar2 );

  PROCEDURE   old_procedure ( old_parm1 in varchar2,
                             old_parm2 in varchar2,
                             old_parm3 in varchar2,
                              NEW_PARM  in varchar2 );

END;
It compiles and works fine with forms 4.5 Received on Sun Dec 27 1998 - 15:36:06 CST