Home Privacy Policy Feedback Link to us Site Map

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.