Re: PL/SQL: optional arguments for procedures/funcs

A copy of this was sent to "Keso" <keso_at_hep.tel.fer.hr> (if that email address didn't require changing) On Wed, 24 Nov 1999 16:19:04 +0100, you wrote:

>Hi PL/SQL gurus !
>
>Can it be determined if optional argument is set by a call or default value
>is used ?
>
>Setting it to some default value (e.g. null) is NOT ENOUGH
>because I need to find out is that null forced by explicit call with that
>(e.g.null) value,
>or the argument was not set because parameter in function call was ommited
>so default value is used.
>
>In short, i need a function like this: IsParameterPresent( parameter_name )
>
>please help,
>
>Vedran
>
>-- using this procedure it is not possible to set name to null, even if
>explicitly called to do so: update_name( null );
>-- update_name() should set name to some default value ('john smith').
>procedure update_name( cName varchar2 default null)
> cNewName varchar2( 2000 );
>begin
> if cName is null then -- is it called by update_name( null ) or
>update_name() (no parameters) ?
> cNewName := 'john smith';
> else
> cNewName := cName;
> end if;
> update person set name = cName;
>end;
>
>

Overloaded procedures will do this for you:

tkyte_at_8.0> create or replace package demo_pkg   2 as
  3 procedure update_name;
  4
  4 procedure update_name( theName in varchar2 );   5 end;
  6 /

Package created.

tkyte_at_8.0>
tkyte_at_8.0> create or replace package body demo_pkg   2 as
  3
  3 procedure update_name
  4 is
  5 begin
  6 dbms_output.put_line( 'update name called without any inputs' );   7 end;
  8
  8 procedure update_name( theName in varchar2 )   9 is
 10 begin
 11 dbms_output.put_line( 'update name called WITH inputs = ' || theName );
 12 end;
 13
 13 end;
 14 /

Package body created.

tkyte_at_8.0>
tkyte_at_8.0> exec demo_pkg.update_name
update name called without any inputs

PL/SQL procedure successfully completed.

tkyte_at_8.0> exec demo_pkg.update_name( 'John Smith' ) update name called WITH inputs = John Smith

PL/SQL procedure successfully completed.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Nov 24 1999 - 09:59:01 CST