Oracle / PLSQL: ORA-06503 Error Message

Learn the cause and how to resolve the ORA-06503 error message in Oracle.

Description

When you encounter an ORA-06503, the following error message will appear:

• ORA-06503: PL/SQL: Function returned without value

Cause

You tried to call a PLSQL function, but the function was missing a RETURN statement.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Try re-writing the function to include a RETURN statement. Or if you don't want to return a value, re-write your function as a procedure.

Every function must return a value. The following is an example of a function called FindCourse that returns a number. The code "RETURN cnumber" ensures that a value is returned from this function.

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;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;