
CREATE OR REPLACE TYPE T_DATE 
AS TABLE OF date;

CREATE OR REPLACE FUNCTION 
GET_DAYS_OF_MONTH (p_year number, p_month number) 
  RETURN t_date pipelined AS 
  v_date_last date;
  v_date date;
BEGIN
  v_date := to_date(p_year||lpad(p_month, 2, 0)||'01', 'yyyymmdd');
  
  v_date_last := last_day(v_date);
  while v_date <= v_date_last loop
  --  v_date := v_date + 1;    
    pipe row(v_date);
    v_date := v_date + to_dsinterval('1 00:00:00');
  end loop;
  
  return;
  
END GET_DAYS_OF_MONTH;


FUNCTION get_emp_per_dep
  ( p_departement_name in varchar2)
  RETURN emp_list pipelined
  is
  t_emp_list emp_list := emp_list();
  c boolean := false;
  e_no_emp_found exception;

  pragma EXCEPTION_INIT
     (e_no_emp_found, -20001);
  
BEGIN
  for emp in (select first_name||' '||last_name name
          from employees e, departments d where
          e.department_id = d.department_id and
          department_name = p_departement_name) loop
  c := true;
--  t_emp_list.extend;
--  t_emp_list(t_emp_list.last) := emp.name;
    pipe row(emp.name);
  end loop;
  
  if c = false then
      raise_application_error(-20001, 'NO emp found in dep ' || p_departement_name);
  end if;
  return;
END;