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

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

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

Cause:

A call to PL/SQL function completed, but no RETURN statement was executed.

Solution:

Rewrite PL/SQL function, making sure that it always returns a value of a proper type.

Example:

create or replace function get_name(p_id number)
return varchar2 as
	v_emp_name varchar2(250);
begin
	select name 
	into v_emp_name 
	from employees 
	where id = p_id;
end;

select get_name(1) from dual;

Output:

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

Correct

create or replace function get_name(p_id number)
return varchar2 as
	v_emp_name varchar2(250);
begin
	select name 
	into v_emp_name 
	from employees 
	where id = p_id;

	return v_emp_name;
end;

select get_name(1) from dual;

Output:

Tom