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;