CREATE PACKAGE hr_pack IS

PROCEDURE get_dept_manager ( dept_id IN numbe(10) );
FUNCTION get_dept_manager_name ( dept_id IN numbe(10) ) return varchar2;

END hr_pack;

CREATE PACKAGE BODY hr_pack IS

PROCEDURE get_dept_manager ( dept_id IN numbe(10) )
IS
empl_id number;
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;
 DBMS_OUTPUT.PUT_LINE('Manager name of dept '||dept_id||' is '||manager_name);
END;

FUNCTION get_dept_manager_name ( dept_id IN numbe(10) ) return varchar2
IS
empl_id number;
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;

END hr_pack;