---

Oracle / PLSQL: Return error message when return datatype is set to NUMBER

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.