OTN Appreciation Day: LISTAGG

Why do I like LISTAGG?

I use it quite often for reporting purposes or when I’m building scripts from metadata, for instance all_tab_cols:
 
 select 'create unique index ' || t1.table_name || '_IDX on '
    || t1.table_name || ' ( '
    || listagg (t1.column_name, ', ')
       within group (order by column_id)
    || ' );' idx_stmt
  from user_tab_cols t1
  where column_name like '%ID'
  group by t1.table_name;
 
which returns statements like this:
 
  create unique index DEPARTMENTS_IDX on DEPARTMENTS ( DEPARTMENT_ID, MANAGER_ID, LOCATION_ID );
 
 

What I don’t like about LISTAGG…

LISTAGG has a limit of 4000 characters.
The only workaround I know is Carsten Czarski’s LISTAGG_CLOB function (German/English).
If you are on 12c, there is another alternative: Instead of the function throwing an error you can have the result truncated by defining the listagg_overflow_clause.
 

The future?

Something that I still miss is the possibility to get rid of duplicates within the results.
Of course I could use DISTINCT in a subquery before I apply LISTAGG, but this only works for one column (if I want to keep it simple). 
If you see a DISTINCT option as an improvement for the LISTAGG function, please support the idea on OTN Database Ideas:
The only thing you need is a (free) OTN account, which you have anyway, don’t you?
 

 

OTN Appreciation Day – what is it?

The idea of the OTN Appreciation Day was initiated by Tim Hall, read more about it here: OTN Appreciation Day
 

Das könnte Sie auch interessieren

Bleiben Sie informiert:

its-people hilft Ihnen...

Weitere Blogthemen:

its-people – wir machen Ihre IT moderner,
leistungsfähiger und sicherer

Erfahren Sie bei einem persönlichen Gespräch, wie wir Sie gewinnbringend unterstützen können. Suchen Sie sich einen passenden Zeitpunkt aus. Wir melden uns. Versprochen!