472,337 Members | 1,416 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,337 software developers and data experts.

how to make oracle function one parameter optional


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)
dbms_output.put_line('The sum of No1 and No2 is ||Resutl);
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 26429
700 Expert 512MB
Why don't you just call it like that
Expand|Select|Wrap|Line Numbers
  1. sum1(5,null,:a)

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. /
Expand|Select|Wrap|Line Numbers
  1. ....
  2. begin
  3. ..
  4. call sum1(No1=>10,Result=>:a);
  5. ...
  6. end;

BTW in this function it won't work (null+integer=null).
Mar 26 '10 #2

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. /
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. /
Mar 26 '10 #3
thank you million time.
Mar 27 '10 #4

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

Similar topics

by: Adam Dyga | last post by:
Hi, How to create function with optional parameter passed by reference? I've tried sth like this: function (&$param=NULL) // or: function...
by: John T | last post by:
I am trying to make a function that takes an optional parameter that gets passed by reference. Here is the first line of my function definition:...
by: Otto Wyss | last post by:
I've the following function declaration: wxTree GetLastChild (const wxTree& item, long& cookie) const; I'd like to make the cookie parameter...
by: Roman Töngi | last post by:
I want to change a vector in a function. I pass a pointer of it to the function and append an item. Then I want to print the first item in the...
by: John | last post by:
My application needs to call Oracle function using oracle client 9.2. The oracle function returns boolean value from its returned parameter. The...
by: Steve Kershaw | last post by:
I'm not even sure this can be done. I have a requirement to call an Oracle function (that returns a value) from C# code. The Oracle function is as...
by: Ronald S. Cook | last post by:
I have a function in which I'm trying to make the parameter optional. However, when I put Optional in front of ByVal below I get the error...
by: Ronald S. Cook | last post by:
In my service project I have this function: Public Function SelectPenTypeTest(Optional ByVal PenTypeID As String = Nothing) As String Implements...
by: giloosh | last post by:
can i pass a hash as a function parameter. ive seen it been used before but i can't figure out how to do it. i would like to call a function like...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.