469,342 Members | 6,453 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

how to make oracle function one parameter optional

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
Mar 26 '10 #1

✓ answered by Rohullah

thank you million time.

3 25275
rski
700 Expert 512MB
Why don't you just call it like that
Expand|Select|Wrap|Line Numbers
  1. sum1(5,null,:a)
  2.  

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

Expand|Select|Wrap|Line Numbers
  1. create function sum1(No1 in number,No2 in number default null,Result out number)
  2. is
  3. begin
  4. Result:=No1+No2;
  5. dbms_output.put_line('The sum of No1 and No2 is ||Resutl);
  6. end;
  7. /
  8.  
Expand|Select|Wrap|Line Numbers
  1. ....
  2. begin
  3. ..
  4. call sum1(No1=>10,Result=>:a);
  5. ...
  6. end;
  7.  

BTW in this function it won't work (null+integer=null).
Mar 26 '10 #2
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:

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE function sum1
  2.               ( No1 in number
  3.                ,No2 in number  default 0
  4.               ) return number
  5. is
  6.    vResult number := 0;
  7. begin
  8.    vResult := No1 + No2;
  9.    dbms_output.put_line('The sum of No1 and No2 is ' || vResult);
  10.    return vResult;
  11. end sum1;
  12. /
  13.  
or, if you don't really need the dbms_output (which I asume is only there for testing):

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE function sum1
  2.               ( No1 in number
  3.                ,No2 in number  default 0
  4.               ) return number
  5. is
  6. begin return No1 + No2; end sum1;
  7. /
  8.  
Mar 26 '10 #3
thank you million time.
Mar 27 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Adam Dyga | last post: by
6 posts views Thread by Otto Wyss | last post: by
6 posts views Thread by Roman Töngi | last post: by
3 posts views Thread by Steve Kershaw | last post: by
3 posts views Thread by Ronald S. Cook | last post: by
2 posts views Thread by Ronald S. Cook | last post: by
3 posts views Thread by giloosh | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.