• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

returning different datatype from a function Oracle PL/SQL

create FUNCTION myfunc
return VARCHAR2
is
v_ret NUMBER;
BEGIN
-- functionality which sets v_ret to -1 or (1/2/4/8)
return v_ret;
END myfunc;

My question is this v_ret will be converted to VARCHAR2 ? Will this create any issues in runtime.?
0
priyanka999kamlekar
Asked:
priyanka999kamlekar
  • 3
  • 2
3 Solutions
 
sdstuberCommented:
it should run and return ok.

problems would only arise if the invoking code tried to do something with the value assuming it was something other than a varchar2.
0
 
priyanka999kamlekarAuthor Commented:
Do you mean this?
i am calling this function myfunc in another function and storing the returned value in the NUMBER variable.

v_ret NUMBER;
v_ret := myfunc();

In this case what will happen?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this will work, as long as the value returned is indeed a "number" value,
given the current code, it shall work, but the "interface declaration" of the function, returning a varchar, it might indeed some day return a non-number function.

so, the best way is either to change the function to RETURN NUMBER instead of RETURN VARCHAR2, or do assume first the function returns a varchar2, and test if the value returned is really a number (or not) before trying to cast/convert it to a number
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
If you return the string '-1'  to v_ret  then v_ret will get an implicit conversion of '-1' the string to -1 the number.

If you return the string '(1/2/4/8)'  then the implicit conversion will be attempted again but it will fail because that is not a number
0
 
sdstuberCommented:
A penalty grade is not appropriate here, I've requested moderator review
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
split among the 3 expert comments
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now