Administration Digression: My Favorite Kind of New Oracle Function, LISTAGG

With the administration of my Discoverer / Noetix environment, I find that sometimes I need to compose comma separated concatenated list of some text column (e.g. for the purpose of creating documentation).

In Oracle Database 11g release 2, the LISTAGG function was introduced which is well suited for this type of task (and no custom function needs to be created).


This function has two flavors, analytic (non-aggregate) and aggregate.Here is a description of the function (see link above for full description in 11g R2):


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LISTAGG ( expression, [delimeter] ) WITHIN GROUP (ORDER BY  order_by_expression_list )

-expression is the expression one wishes to concatenate
-delimeter is self-explanatory (e.g. ',')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The analytic flavor is invoked as follows:

LISTAGG ( expression, [delimeter] ) WITHIN GROUP (ORDER BY  order_by_expression_list )

 [OVER (PARTITION BY partition_columns)]


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


With some documentation I am creating, I need to create a concatenated list of Discoverer responsibiities.


Here is my invocation of this function using the aggregate version of this function:


 EUL5_US@erpdev> SELECT LISTAGG(responsibility_name, ', ') WITHIN GROUP (
  2  ORDER BY responsibility_name) Responsibility_list
  3  FROM applsys.fnd_responsibility_tl
  4  WHERE 1      =1
  5  AND language = SYS_CONTEXT ('USERENV', 'LANG')
  6  AND regexp_like( responsibility_name, 'Discoverer.+ - \w{2}')
  7  /

Responsibility_list
------------------------------------------------------------------------
Discoverer Commissions - CS, Discoverer Viewer - CA, Discoverer Viewer - CM, Discoverer Viewer - CS, Discoverer Viewer - CW