drop type emptype_t
/
drop type emptype
/
create or replace type emptype
                       as object(empno number, ename varchar2(30));
/
create or replace type emptype_t 
                       as table of emptype;
/
create or replace package Employee as
  function getByEname(p_ename in varchar2) return emptype_t;
END;
/
create or replace PACKAGE BODY EMPLOYEE AS
  function getByEname(p_ename in varchar2) return emptype_t is
    t_emp emptype_t;
  begin
    select emptype(empno, ename) 
      bulk collect into t_emp 
      from emp 
      where ename like p_ename;
    return t_emp;    
  exception when no_data_found then
    return null;
  end getByEname;
END;
/