ORA-06503: PL/SQL: Function returned without value in Oracle

Reason why this error occurs??

1) Function does not have return statement.

2) A null value is returned and no exception handling

3) A null value is returned to a variable, and that variable is further being used in the same function.

Solutions

1) Simply return the variable which contains the result value.

2) You need to do exception handling in the function.

Exception when others then

       return 0;       /* Return any number for Number return type of function*/

       return 'abc';  /* You can return number/character for Character return type of function*/

3) I have mentioned an example below.

We created a function in which employee number is passed as parameter n we place its hire date into var1.  if var1 is greater than '01-JAN-2012' then return 100 else return 0;

create or replace function test (p_emp_no in number)

   return number

is

   result  number;

   var1       number;

begin

  select ppf.original_date_of_hire into var1 from per_people_f ppf

   where employee_number = p_emp_no;

  

   if var1 > '01-JAN-2012' then

        result = 100;

   else

        result =  0;

   end if;

   return nvl(result,0); 

exception

   when others

   then

      return 0;

end;

/

Function will work fine unless an employee number is passed which has no hire date. Function will go into exception. So you would not know what's the error.

if we remove the exception, we get the error, ORA-06503: PL/SQL: Function returned without value

To correct this issue, what we need to do is...Please see the code below in red.

Place a Begin End block around that query and place exception in this block as well. if employee has no hire date, you may return some dummy value.

BEGIN

select ppf.original_date_of_hire into var1 from per_people_f ppf 

   where employee_number = p_emp_no;

Exception when others then

    var1 = '01-JAN-2001';

END;

so var1 would contain some value before it is passed to condition used below.

Feedback is very important for my improvement. So please do comment.