Question: I've created a function in PLSQL that returns a number. How can I get this function to return an error message such as 'Course number does not exist!', when the return datatype is set to NUMBER, instead of VARCHAR2?
Answer: How about forcing the function to return an unusual number such as 99999 when the course is not found. Then whenever you evaluate the results from your function, check to see if the value is 99999. If it is, then generate your error message.
For example:
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
cursor c1 is
select course_number
from courses_tbl
where course_name = name_in;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;
RETURN cnumber;
END;
This function is called FindCourse. It has one parameter called name_in and it returns a number. The function will return the course number if it finds a match based on course name. Otherwise, it returns a 99999.
