returning different datatype from a function Oracle PL/SQL

create FUNCTION myfunc
return VARCHAR2
v_ret NUMBER;
-- 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.?
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor 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
sdstuberConnect With a Mentor Commented:
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.
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?
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

sdstuberConnect With a Mentor Commented:
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
A penalty grade is not appropriate here, I've requested moderator review
Guy Hengel [angelIII / a3]Billing EngineerCommented:
split among the 3 expert comments
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.