How to resolve ORA-06503 Error Message in Oracle database

Learn the reason and how to unravel the ORA-06503 error message in Oracle.

Description

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

ORA-06503: PL/SQL: Function back without cost

Cause

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

Resolution

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

Option #1

Try re-writing the feature to consist of a RETURN statement. Or if you do not favor to return a value, re-write your feature as a procedure.

Every feature must return a value. The following is an instance of a feature referred to as FindCourse that returns a number. The code “RETURN cnumber” ensures that a fee is lower back 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;