Wednesday 9 September 2009

Oracle : 11GR2 - LISTAGG - the long missing string concatentation built-in

Finally

LISTAGG Example
===============
SELECT department_id "Dept.",
  LISTAGG(last_name, '; ')
   WITHIN GROUP (ORDER BY hire_date) "Employees"
   FROM employees
   GROUP BY department_id;

Dept. Employees
------ ------------------------------------------------------------
    10 Whalen
    20 Hartstein; Fay
    30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
    40 Mavris
    50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
       s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
       ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Pat
       el; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers;
        Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sulliv
       an; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle
    60 Austin; Pataballa; Lorentz; Ernst; Hunold
    70 Baer

  • Throws a ORA-01489: result of string concatenation is too long is > 4000 chars ! -
  • No way to suppress (?) so need to avoid
with base_data as (
select
table_name,
sum(length(table_name)) over (order by table_name)
  + row_number () over (order by table_name)        tot_length
from dba_tables
where 1=1
and owner = 'SYS'
order by table_name
)
select
listagg(table_name,';') within group (order by table_name)
from base_data b
where tot_length < 4000

Ref



0 comments: