Home » SQL & PL/SQL » SQL & PL/SQL » Function with Out parameter
Function with Out parameter [message #469610] Thu, 05 August 2010 05:31 Go to next message
Genesys
Messages: 45
Registered: August 2010
Member
Hi All,

iam new to this forum.can any one give the hint regarding the functions.

if function having retrun statement, why we need to use out or inout parameters.

thanks
Genesys
Re: Function with Out parameter [message #469611 is a reply to message #469610] Thu, 05 August 2010 05:33 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
You don't and you shouldn't.
If you've got IN OUT or OUT parameters in a function then you can't use it in sql.
If you want something with multiple out parameters use a procedure.
Re: Function with Out parameter [message #469615 is a reply to message #469611] Thu, 05 August 2010 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I disagree, you can use it when you need it and have to not use it only when it is not allowed (like in SQL statament).

Regards
Michel
Re: Function with Out parameter [message #469622 is a reply to message #469615] Thu, 05 August 2010 05:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
To be formal about it. Using an out or in/out parameter in a function is considered poor form. As we all know, a function is designed to return only one value. When out or in/out parameters are applied to a function "SIDE-EFFECTS" can occur. I will not post any here, only to say that considerable research has been done by well known names on the subject. If you doubt this, go find a local experienced C or C++ programmer. They will tell you all about function side effects caused by functions that return data in addition to their return value.

That said, I have seen code from Oracle Corp. that has done this. Who would have guessed. As I recall however, it was only to return an error message and error code (but my memory is fading so don't quote me on that).

I agree with cookie monster. If you need a routine that returns more than one value, use a procedure.

Kevin
Re: Function with Out parameter [message #469627 is a reply to message #469610] Thu, 05 August 2010 06:05 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Genesys wrote on Thu, 05 August 2010 12:31
if function having retrun statement, why we need to use out or inout parameters.

You do not need to use them, they are optional (not mandatory). On the other hand, why (the others) should not use them when they feel they are suitable and its usage complies their coding standards?
Re: Function with Out parameter [message #469629 is a reply to message #469627] Thu, 05 August 2010 06:10 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can use them if you really want to but I really don't see the point.
Since you instantly limit the usage to PL/SQL it's as easy to use a procedure and generally less confusing.
I've seen on more than one occasion people wondering why they can't call functions from SQL.
Re: Function with Out parameter [message #469635 is a reply to message #469629] Thu, 05 August 2010 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can give a rule based on low "skillessness" of people.
It is sometimes useful, it is sometimes required by coding standards, so there is no need to set "not do it" as a rule when there are no real drawback (unless you can tell some).

Regards
Michel
Re: Function with Out parameter [message #469715 is a reply to message #469635] Thu, 05 August 2010 11:16 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
The only coding standard (regarding this) I have ever come across always was the other way around: do not use functions with out parameters.
Actually, I cannot think why a coding standard would state you are required to use a function for anything, even if there are out parameters. (Note: talking about standards here, not about the use of the functions itself)
Re: Function with Out parameter [message #469725 is a reply to message #469715] Thu, 05 August 2010 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I often saw a coding standard "à la C" where the returned value of the function is a return code and functional values are returned in OUT parameter.

Regards
Michel
Re: Function with Out parameter [message #469733 is a reply to message #469610] Thu, 05 August 2010 11:59 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Well for me the whole point of a function is chaining.

x := strip_first_letter(strip_first_letter(strip_first_letter(x)));

would strip the first three letters from X; If the function is written like this:

function strip_first_letter(in_p in varchar2) return varchar2 is
begin
   ...
end;
/

it makes some sense; But what does this mean?

function strip_first_letter(
                               in_p in varchar2
                              , out1_p out varchar2
                              , out2_p out varchar2
                              , out3_p out varchar2
                            ) return varchar2 is
begin
   ...
end;
/

What does one do with the out parameters when chaining?
Is it even possible to chain?

If a function can't be used to chain and it can't be used in a sql statement, then why make the piece of code a function? Neither of the two reasons for choosing function as the code type work.

Kevin

[Updated on: Thu, 05 August 2010 12:02]

Report message to a moderator

Re: Function with Out parameter [message #469737 is a reply to message #469733] Thu, 05 August 2010 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because in many languages there is ONLY functions and in many entreprises coding standards are the same ones for all languages unless they do not support one of the rules.

Note I do NOT advocate for this use of functions. I only say there are many entreprises they are required and so can't be exclude.

For myself, I prefer to use functions for the purposes that Kevin mentioned in his last sentence and procedures otherwise and ALWAYS use exceptions to return error "code".

Regards
Michel
Re: Function with Out parameter [message #469765 is a reply to message #469737] Thu, 05 August 2010 14:09 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
I also agree to have out parameters with functions.
As an Oracle Applications Developer, I've seen this kind of coding in Functions.
The key module, Application Object Library (FND) has lot many examples.

By
Vamsi
Re: Function with Out parameter [message #469810 is a reply to message #469765] Thu, 05 August 2010 21:29 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
 create or replace function fnProduct ( A in number , Res Out number ) return number
     is
    begin
       res := a * 10;
       return res;
     end;
    /

Function created.


var A number;
var R number;

set serverout on 

exec :A  := fnProduct (100,:R );


exec  dbms_output.put_line(:R);

1000

PL/SQL procedure successfully completed.

Re: Function with Out parameter [message #469812 is a reply to message #469610] Thu, 05 August 2010 21:50 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
rahulvb What is the advantage? as compared to a procedure with 2 out parameters?

Kevin
Re: Function with Out parameter [message #469813 is a reply to message #469812] Thu, 05 August 2010 22:02 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
So does the Function : but i have put in the code just to illustrate to another view as OP wanted OUT in function.

Nothing Special About it Smile

Elapsed: 00:00:00.56
SQL> create or replace function fnProduct ( A in number , Res Out number,Res1 Out number ) return number
  2       is
  3      begin
  4         res := a * 10;
  5         res1 := a * 100;
  6         return res;
  7       end;
  8      /

Function created.

Elapsed: 00:00:00.54
SQL> 
SQL> var A number;
SQL> var B number;
SQL> var C number;
SQL> set serverout on
SQL> 
SQL> exec :A  := fnProduct (100,:B,:C );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.53
SQL> exec  dbms_output.put_line(:B || ' , ' || :C);
1000 , 10000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.53
SQL> 

Re: Function with Out parameter [message #469826 is a reply to message #469813] Fri, 06 August 2010 00:38 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
The original poster did not ask for what you provided, (s)he asked what use there was in having out parameters in functions.
Now Kevin pointed this out to you, so you repost your useless example. Great. Maybe after this post you can post an example of a packaged function with out parameters.

"Michel wrote"
Because in many languages there is ONLY functions and in many entreprises coding standards are the same ones for all languages unless they do not support one of the rules.

But if they were consistent in their standards, they would require a struct return type instead of a scalar + out parameters.
Re: Function with Out parameter [message #469827 is a reply to message #469826] Fri, 06 August 2010 00:51 Go to previous message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Frank wrote on Fri, 06 August 2010 00:38
so you repost your useless example. Great.



May be it is Useless..May be not !!!

Frank wrote on Fri, 06 August 2010 00:38

But if they were consistent in their standards, they would require a struct return type instead of a scalar + out parameters.


????
Previous Topic: ORA- 04022 Error
Next Topic: Entering empty string into Primary Key results in error!?!?!
Goto Forum:
  


Current Time: Sat Apr 27 09:07:42 CDT 2024