SELECT LENGTH(my_list) AS length_of_list 
     , SUBSTR(my_list,-100) AS tailend 
  FROM ( 
SELECT LISTAGG (e.last_name||', '||e.first_name 
            ||' (in '||d.department_name 
            ||' as '||j.job_title 
            ||' at '||l.city||', '||l.state_province 
            ||', '||c.country_name ||')' 
               ,'; ' on overflow truncate with count)     
                within group (order by e.last_name 
                                     , e.first_name 
                                     , d.department_name 
                                     , j.job_id) AS my_list 
  FROM hr.employees e 
  LEFT OUTER 
  JOIN hr.departments d 
    ON e.department_id = d.department_id 
  LEFT OUTER 
  JOIN hr.locations L 
    ON L.location_id = d.location_id 
  LEFT OUTER 
  JOIN hr.countries c 
     ON c.country_id = L.country_id 
  CROSS--LEFT OUTER 
  JOIN hr.jobs j 
    --ON e.job_id = j.job_id 
)