with 
deals_base as (
  select /*+ inline*/ 
    id, from_date, to_date, client_id
  from (
    select 1 id, to_date('02.08.2019', 'dd.mm.yyyy') from_date, to_date('01.01.3000', 'dd.mm.yyyy') to_date, 2 client_id from dual union all
    select 3 id, to_date('08.02.2019', 'dd.mm.yyyy') from_date, to_date('01.01.3000', 'dd.mm.yyyy') to_date, 4 client_id from dual
  )
),
clients_base as (     
  select /*+ inline*/
    client_id, from_date, to_date
  from (            
    select 2 client_id, to_date('01.01.2019', 'dd.mm.yyyy') from_date, to_date('05.08.2019', 'dd.mm.yyyy') to_date from dual union all
    select 2 client_id, to_date('06.08.2019', 'dd.mm.yyyy') from_date, to_date('01.01.3000', 'dd.mm.yyyy') to_date from dual union all
    select 4 client_id, to_date('05.02.2019', 'dd.mm.yyyy') from_date, to_date('01.10.2019', 'dd.mm.yyyy') to_date from dual union all
    select 4 client_id, to_date('02.10.2019', 'dd.mm.yyyy') from_date, to_date('01.01.3000', 'dd.mm.yyyy') to_date from dual
  )
)
,t as (
 select id,from_date from deals_base where client_id=2
 union all /* union */
 select 0,from_date from clients_base c where client_id=2 
            and  not exists(select 1 from deals_base d where d.client_id=c.client_id and d.from_date=c.from_date)
)
select t.*
 ,lead(from_date,1,to_date('01.01.3000', 'dd.mm.yyyy')) over (order by from_date)-1  to_date
from t order by 2
/
SQL> /

        ID FROM_DATE  TO_DATE
---------- ---------- ----------
         0 01.01.2019 01.08.2019
         1 02.08.2019 05.08.2019
         0 06.08.2019 31.12.2999

WITH deals_base AS ( -- а-ля в таблица в базе
                    SELECT 1 AS ID,
                           to_date('02.08.2019', 'dd.mm.yyyy') AS from_date,
                           to_date('04.08.2019', 'dd.mm.yyyy') AS to_date,
                           2 AS client_id
                      FROM dual
                    UNION ALL
                    SELECT 1 AS ID,
                           to_date('05.08.2019', 'dd.mm.yyyy') AS from_date,
                           to_date('21.08.2019', 'dd.mm.yyyy') AS to_date,
                           2 AS client_id
                      FROM dual
                    UNION ALL
                    SELECT 1 AS ID,
                           to_date('22.08.2019', 'dd.mm.yyyy') AS from_date,
                           to_date('01.01.3000', 'dd.mm.yyyy') AS to_date,
                           2 AS client_id
                      FROM dual

create or replace FUNCTION stax_pp
return stax_ot
PIPELINED is
 v_id number;
 v_cl number;
 v_dat date;
 v_idx varchar2(8);
 type t_dat is table of date index by varchar2(8); --index by date не спахало
 vt_dat t_dat;
 procedure p_client(p_cid number,p_did number) is
 begin
   for rr in (select * from clients_base where client_id=p_cid) loop
     vt_dat(to_char(rr.from_date,'yyyymmdd')):=rr.from_date;
   end loop;
 end;
begin
  for r in (select * from deals_base order by id /*,from_date / to_date */) loop
     if nvl(v_id,r.id)<>r.id then
        p_client(v_cl,v_id);
/*
        --почему-то не сработало, не знаю почему
        for i in vt_dat.first .. vt_dat.last loop
           pipe row(stax_o(v_id,
        end loop;
*/
        --надеюсь что по дате отсортировано из=за index by, иначе упорядочить
        v_idx := vt_dat.first;
        WHILE (v_idx IS NOT NULL)
        LOOP
               v_dat:=vt_dat(v_idx);
               v_idx := vt_dat.NEXT(v_idx);
               pipe row(stax_o(v_id,v_dat,
                   (case when v_idx is null then to_date('01.01.3000', 'dd.mm.yyyy') else vt_dat(v_idx)-1 end)));
        END LOOP;
        vt_dat.delete; --мож и покрасивее есть очистка
     end if;
     v_id:=r.id;
     v_cl:=r.client_id;
     vt_dat(to_char(r.from_date,'yyyymmdd')):=r.from_date;
  end loop;
  p_client(v_cl,v_id);
  v_idx := vt_dat.first;
  WHILE (v_idx IS NOT NULL)
  LOOP
       v_dat:=vt_dat(v_idx);
       v_idx := vt_dat.NEXT(v_idx);
       pipe row(stax_o(v_id,v_dat,
               (case when v_idx is null then to_date('01.01.3000', 'dd.mm.yyyy') else vt_dat(v_idx)-1 end)));
  END LOOP;
end;
/
SQL> select * from deals_base;

        ID FROM_DATE  TO_DATE     CLIENT_ID
---------- ---------- ---------- ----------
         1 02.08.2019 01.01.3000          2
         3 08.02.2019 01.01.3000          4

SQL> select * from clients_base;

 CLIENT_ID FROM_DATE  TO_DATE
---------- ---------- ----------
         2 01.01.2019 05.08.2019
         2 06.08.2019 01.01.3000
         4 05.02.2019 07.08.2019
         4 08.02.2019 01.01.3000

SQL> select * from table(stax_pp);

        ID DATE_FROM  DATE_TO
---------- ---------- ----------
         1 01.01.2019 01.08.2019
         1 02.08.2019 05.08.2019
         1 06.08.2019 01.01.3000
         3 05.02.2019 07.02.2019
         3 08.02.2019 01.01.3000

        --почему-то не сработало, не знаю почему
        for i in vt_dat.first .. vt_dat.last loop
               dbms_output.put_line('pipe '||v_id||' '||vt_dat(i)||' i='||i);
               pipe row(stax_o(v_id,vt_dat(i),vt_dat(I)));
        end loop;

create or replace FUNCTION stax_pp2
return stax_ot
PIPELINED is
 v_id number;
 v_cl number;
 v_dat date;
 v_idx varchar2(8);
 type t_dat is table of date index by varchar2(8); --index by date не спахало
 vt_dat t_dat;
 procedure p_client(p_cid number,p_did number) is
 begin
   for rr in (select * from clients_base where client_id=p_cid) loop
     vt_dat(to_char(rr.from_date,'yyyymmdd')):=rr.from_date;
     dbms_output.put_line('client '||p_did||' '||rr.from_date||' '||vt_dat.count);
   end loop;
 end;
begin
  for r in (select * from deals_base order by id /*,from_date / to_date */) loop
     dbms_output.put_line('deal '||r.id||' '||r.from_date||' '||vt_dat.count);
     if nvl(v_id,r.id)<>r.id then
        p_client(v_cl,v_id);
        --почему-то не сработало, не знаю почему
        for i in vt_dat.first .. vt_dat.last loop
               dbms_output.put_line('pipe '||v_id||' '||vt_dat(i)||' i='||i);
               pipe row(stax_o(v_id,vt_dat(i),vt_dat(I)));
        end loop;
/*
        --надеюсь что по дате отсортировано из=за index by, иначе упорядочить
        v_idx := vt_dat.first;
        WHILE (v_idx IS NOT NULL)
        LOOP
               v_dat:=vt_dat(v_idx);
               v_idx := vt_dat.NEXT(v_idx);
               pipe row(stax_o(v_id,v_dat,
                   (case when v_idx is null then to_date('01.01.3000', 'dd.mm.yyyy') else vt_dat(v_idx)-1 end)));
        END LOOP;
*/
        vt_dat.delete;
     end if;
     v_id:=r.id;
     v_cl:=r.client_id;
     vt_dat(to_char(r.from_date,'yyyymmdd')):=r.from_date;
  end loop;
  p_client(v_cl,v_id);
  v_idx := vt_dat.first;
  WHILE (v_idx IS NOT NULL)
  LOOP
       v_dat:=vt_dat(v_idx);
       v_idx := vt_dat.NEXT(v_idx);
       pipe row(stax_o(v_id,v_dat,
               (case when v_idx is null then to_date('01.01.3000', 'dd.mm.yyyy') else vt_dat(v_idx)-1 end)));
  END LOOP;
end;
/
SQL> select * from table(stax_pp2);

        ID DATE_FROM  DATE_TO
---------- ---------- ----------
         1 01.01.2019 01.01.2019

deal 1 02.08.2019 0
deal 3 08.02.2019 1
client 1 01.01.2019 2
client 1 06.08.2019 >>3<<<
pipe 1 01.01.2019 i=20190101
SQL>
SQL> select * from deals_base order by id;

        ID FROM_DATE  TO_DATE     CLIENT_ID
---------- ---------- ---------- ----------
         1 02.08.2019 01.01.3000          2
         3 08.02.2019 01.01.3000          4

SQL> select * from clients_base ;

 CLIENT_ID FROM_DATE  TO_DATE
---------- ---------- ----------
         2 01.01.2019 05.08.2019
         2 06.08.2019 01.01.3000
         4 05.02.2019 07.08.2019
         4 08.02.2019 01.01.3000