CREATE OR REPLACE FUNCTION get_dept_manager_name ( dept_id IN number ) return varchar2
IS
empl_id number;
manager_name varchar2(50);
BEGIN
 select max(employee_id) into empl_id from hr.employees where job_id like '%_MAN' and department_id = dept_id;
 select first_name||' '||last_name into manager_name from hr.employees where employee_id = empl_id;
 return manager_name;
END;

select get_dept_manager_name(50) as Manager_name from dual;
 