Re: Can a function() be used to change parameter value in stored procedures ?

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 19 Mar 2004 21:02:03 -0500
Message-ID: <IMSdnRQpffaoO8bd4p2dnA@comcast.com>

"sui generis" <sui_generis71_at_hotmail.com> wrote in message news:c8cbc560.0403190746.77fae429_at_posting.google.com...
| I was wondering if i can use a function() to change a parameter value
| ..
| this is what i want to.. if the input value to the stored procedure is
| NULL then i want to change it to some other value..
|
| can i use something like ..
|
| procedure test
| (
| param1 in varchar2 default chk_function();
| )
| ...
| the chk_function() will check whether the input value is null or not
| and if null ,replaces it with a default value ?

sounds like you need oracle's NVL function

however, if the user passes an explicit NULL to the parameter, the default value will be ignored -- so you will need to do the check in the body of the procedure

sample:

create or replace procedure nulltest( x number default 0 ) is
  str varchar2(30);
begin

      if x is null
      then str := 'x is null';
      else str := 'x = ' || x;
      end if;

  dbms_output.put_line( str );
end;

SQL> exec nulltest
x = 0
SQL> exec nulltest(1)
x = 1
SQL> exec nulltest(null)
x is null

;-{ mcs Received on Fri Mar 19 2004 - 20:02:03 CST