SQL> with kontrakt as (
  2    select 1 id,'F-1212-a' n from dual union all
  3    select 2 id,'I-XDC3-b' n from dual
  4    )
  5  ,tarif as (
  6    select 1 id,1 kontrakt_ref,'Связь' k from dual union all
  7    select 2 id,1,'Интернет'             from dual union all
  8    select 3 id,1,'Телевидение'          from dual union all
  9    select 5 id,2,'Интернет'             from dual)
 10  select max(sum(length(k))) max_sum_length
 11   from kontrakt k,tarif t
 12   where t.kontrakt_ref=k.id
 13   group by k.id
 14  /

MAX_SUM_LENGTH
--------------
            24

SQL> col LIST_TARIF format a24


SQL> with kontrakt as (
  2    select 1 id,'F-1212-a' n from dual union all
  3    select 2 id,'I-XDC3-b' n from dual
  4    )
  5  ,tarif as (
  6    select 1 id,1 kontrakt_ref,'Связь' k from dual union all
  7    select 2 id,1,'Интернет'             from dual union all
  8    select 3 id,1,'Телевидение'          from dual union all
  9    select 5 id,2,'Интернет'             from dual)
 10  select k.id,max(k.n) n,listagg(t.k) within group(order by null) list_tarif
 11   from kontrakt k,tarif t
 12   where t.kontrakt_ref=k.id
 13   group by k.id
 14  /

        ID N        LIST_TARIF
---------- -------- ------------------------
         1 F-1212-a ИнтернетСвязьТелевидение
         2 I-XDC3-b Интернет