how to make oracle function one parameter optional

[Rohullah]

Hello

i want to make optional a parameter in oracel function
for example this is my function

create function sum1(No1 in number,No2 in number,Result out number)
is
begin
Result:=No1+No2;
dbms_output.put_line('The sum of No1 and No2 is ||Resutl);
end;
/
variable a number;

Now i want to call it like this

call sum1(10,:a);

i mean that i want to leave the No2 parameter as null, and also give the result
how should i do
give me the best result
thank u everyone

[rski]

Why don't you just call it like that

sum1(5,null,:a)
 

If you can't do such call do it like that

create function sum1(No1 in number,No2 in number default null,Result out number)
is
begin
Result:=No1+No2;
dbms_output.put_line('The sum of No1 and No2 is ||Resutl);
end;
/
 

....
begin
..
call sum1(No1=>10,Result=>:a);
...
end;
 

BTW in this function it won't work (null+integer=null).

[magicwand]

Rohullah,

your function has 3 major errors and one considerable flaw aside from your "default" problem:

1.) Functions always must have a return value (otherwise it's a procedure)

2.) Every arithmetic operation with a NULL operand results in NULL (this means, if you set No2 to a NULL - default, all calls with just one parameter will result in NULL
So you have to default your No2 - parameter to 0 (the number zero).

3.) There are a missing quote and a typo in your call to the dbms_output - package

the flaw:

Although it is syntactically correct to have "OUT" - parameters in a function, it is widely considered as very, very poor programming style to use such constructs.
This is called "side effect programming" and would not necessarily raise your reputation ...

My recommendations for this function:

CREATE OR REPLACE function sum1
              ( No1 in number
               ,No2 in number  default 0
              ) return number
is
   vResult number := 0;
begin
   vResult := No1 + No2;
   dbms_output.put_line('The sum of No1 and No2 is ' || vResult);
   return vResult;
end sum1;
/
 

or, if you don't really need the dbms_output (which I asume is only there for testing):

CREATE OR REPLACE function sum1
              ( No1 in number
               ,No2 in number  default 0
              ) return number
is
begin return No1 + No2; end sum1;
/
 

[Rohullah]

thank you million time.