create table emp_time as 
WITH EMP AS 
(
SELECT 1 EMP_ID, 'FRED' EMP_NAME FROM DUAL UNION ALL
SELECT 2,'MICHEL' FROM DUAL UNION ALL
SELECT 3,'SCOTT'  FROM DUAL UNION ALL
SELECT 4,'JOHN'   FROM DUAL UNION ALL
SELECT 5,'ALEX'   FROM DUAL UNION ALL
SELECT 6,'PAUL'   FROM DUAL UNION ALL
SELECT 7,'JEAN'   FROM DUAL UNION ALL
SELECT 8,'TOM'   FROM DUAL UNION ALL
SELECT 9,'STEVE'   FROM DUAL UNION ALL
SELECT 10,'TED'   FROM DUAL 
),
RAW_DATA AS
(
SELECT E.EMP_ID, E.EMP_NAME, TO_DATE('01-JAN-14')+R.L WORK_DATE
FROM EMP E,(SELECT LEVEL-1 L FROM DUAL CONNECT BY LEVEL<=31) R
WHERE 1=1
),
RND_TIMING AS 
(
SELECT 1 T_ID, '00:00' S_TIME, '05:59' E_TIME FROM DUAL UNION ALL
SELECT 1,'11:00' , '23:59' FROM DUAL UNION ALL

SELECT 2,'00:00' , '10:59' FROM DUAL UNION ALL
SELECT 2,'06:00' , '20:59' FROM DUAL UNION ALL
SELECT 2,'11:00' , '23:59' FROM DUAL UNION ALL

SELECT 3,'00:00' , '23:59' FROM DUAL UNION ALL
SELECT 3,'21:00' , '23:59' FROM DUAL UNION ALL

SELECT 4,'00:00' , '10:59' FROM DUAL UNION ALL
SELECT 4,'11:00' , '19:59' FROM DUAL UNION ALL

SELECT 5,'00:00' , '05:59' FROM DUAL UNION ALL
SELECT 5,'06:00' , '23:59' FROM DUAL UNION ALL
SELECT 5,'20:00' , '23:59' FROM DUAL UNION ALL

SELECT 6,'00:00' , '23:59' FROM DUAL
)
SELECT A.EMP_ID, A.EMP_NAME, A.WORK_DATE,B.S_TIME,B.E_TIME
FROM (SELECT EMP_ID, EMP_NAME, WORK_DATE,TRUNC(DBMS_RANDOM.VALUE(1,7)) RD FROM RAW_DATA) A
LEFT JOIN RND_TIMING B ON A.RD=B.T_ID;

   EMP_ID EMP_NAME WORK_DATE S_TIME E_TIME
---------- -------- --------- ------ ------
         1 FRED     01-JAN-14 00:00  23:59  
         1 FRED     01-JAN-14 21:00  23:59  
         1 FRED     02-JAN-14 00:00  10:59  
         1 FRED     02-JAN-14 11:00  19:59  
         1 FRED     03-JAN-14 00:00  23:59  
         1 FRED     03-JAN-14 21:00  23:59  
         1 FRED     04-JAN-14 00:00  23:59  
         1 FRED     04-JAN-14 21:00  23:59  
         1 FRED     05-JAN-14 00:00  10:59  
         1 FRED     05-JAN-14 06:00  20:59  
         1 FRED     05-JAN-14 11:00  23:59  

--create a varray to hold time window variables
create or replace type time_w is varray(50) of varchar2(5);
/
--create a function that to collect time window variables
create or replace function time_w_func(csv_list in varchar2) return time_w is 
str varchar2(32767):= csv_list || ',';
COMMA_ID PLS_INTEGER;
start_id pls_integer := 1;
I PLS_INTEGER := 1;
outvect time_w:=time_w();
begin
case when LENGTH(str)>1 then
loop
  comma_id := instr(str, ',', start_id);
  exit when comma_id = 0;
  outvect.extend;
  outvect(i):=substr(str, start_id, comma_id - start_id);
  start_id := comma_id + 1;
  i:=i+1;
END LOOP;
return outvect;
ELSE 
return time_w();
end case;
end time_w_func;
/
--create a function that looks-up an item
create or replace function get_time_w(inlist time_w, i integer) return varchar2 is
begin
if inlist.exists(1) then 
  if i<=inlist.last then 
    return inlist(i);
  else
    return null;
  end if;
else
return null;
end if;
end get_time_w;
/
--create a function to get time difference
CREATE OR REPLACE FUNCTION TIME_DIF(S_T VARCHAR2, E_T VARCHAR2) RETURN NUMBER IS
time_dif number;
BEGIN
if s_t<e_t then
time_dif:=(to_number(substr(E_T,1,2)*60)+to_number(substr(E_T,4,2)))-
          (TO_NUMBER(SUBSTR(S_T,1,2)*60)+TO_NUMBER(SUBSTR(S_T,4,2)));
ELSE
TIME_DIF:=-1*((TO_NUMBER(SUBSTR(S_T,1,2)*60)+TO_NUMBER(SUBSTR(S_T,4,2)))-
          (to_number(substr(e_T,1,2)*60)+to_number(substr(e_T,4,2))));
end if;
RETURN time_dif;
END TIME_DIF;
/

CREATE TABLE emp_PVT AS 
SELECT EMP_ID,EMP_NAME,WORK_DATE,
TIME_W_FUNC(LISTAGG(t, ',') within group (order by t ASC)) t
FROM(
SELECT EMP_ID,EMP_NAME,WORK_DATE,S_TIME T
from emp_time
union
select EMP_ID,EMP_NAME,WORK_DATE,e_TIME T
from emp_time
)
GROUP BY EMP_ID,EMP_NAME,WORK_DATE;

--this is to hold the result, needs to be truncated
create table EMP_OUTPUT as 
SELECT EMP_ID,EMP_NAME,WORK_DATE,S_TIME,E_TIME
FROM EMP_TIME
where ROWNUM=1;
/

set timing on;
set serveroutput on;
declare
cursor cur is 
select emp_id,emp_name,work_date,t
from emp_pvt;
type tab_type is table of cur%rowtype;
tab tab_type;
res tab_type;
x integer:=0;
p integer;
tmp_tw time_w;
begin
res:=tab_type();
select emp_id,emp_name,work_date,t
bulk collect into tab
from emp_pvt;

for i in tab.first..tab.last loop  
res.extend;
res(i).emp_id:=tab(i).emp_id;
res(i).emp_name:=tab(i).emp_name;
res(i).work_date:=tab(i).work_date;
res(i).t:=tab(i).t;

x:=tab(i).t.count/2;
for k in 1..(x-1) loop
if  time_dif(get_time_w(tab(i).t,k*2),get_time_w(tab(i).t,(k*2)+1))<>1 then 
 res(i).t.extend(2);
 res(i).t(res(i).t.count-1):=
   to_char(to_date(get_time_w(tab(i).t,k*2),'hh24:mi:ss')-1/60/24,'hh24:mi');
 res(i).t(res(i).t.count):=
   to_char(to_date(get_time_w(tab(i).t,(k*2)+1),'hh24:mi:ss')+1/60/24,'hh24:mi');
end if;
end loop;
select cast(multiset(select * from table(res(i).t) order by 1) as time_w)
into res(i).t from dual;
end loop;

execute immediate 'truncate table emp_output drop storage';

for v in res.first..res.last loop
  p:=1;
  while 2*p <=res(v).t.count loop
    insert into emp_output (emp_id,emp_name,work_date,s_time,e_time)  
    values (res(v).emp_id,res(v).emp_name,res(v).work_date,res(v).t(2*p-1),res(v).t(2*p));
    p:=p+1;
  end loop;
end loop;

end;
/