Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Balakrishna.

Asked: June 07, 2016 - 10:26 am UTC

Last updated: September 29, 2020 - 3:57 am UTC

Version: 12c

Viewed 50K+ times! This question is

You Asked



Hi tom,
when i tried use this wm_concat() function in 12c i'm getting this error can you briefly explain?

ORA-00904: "WM_CONCAT": invalid identifier
00904. 00000 - "%s: invalid identifier

and Connor said...

WM_CONCAT is gone in 12c.

Use LISTAGG instead

SQL> select deptno, listagg(ename, ',') within group (order by ename) as employees
  2  from   emp
  3  group by deptno;

    DEPTNO EMPLOYEES
---------- ------------------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

RE

George Joseph, June 08, 2016 - 6:31 am UTC

Just to add some information. wm_concat was an undocumented feature and therefore should not have been used.

https://oracle-base.com/articles/misc/string-aggregation-techniques#wm_concat

I learnt it the hard way :)

Boobal Ganesan, June 09, 2016 - 10:34 pm UTC

Hello Connor,

Why does Oracle allows undocumented features to be used in the first place when it is not supported?

Compared to LISTAGG which aggregating only 4000 characters max, wm_concat aggregates characters more than 4000 in length, which is still better than LISTAGG.

Thank you,
Boobal Ganesan


Connor McDonald
June 10, 2016 - 3:03 am UTC

"Why does Oracle allows undocumented features to be used in the first place when it is not supported? "

We didnt *say* "Hey everyone! Go out and use WM_CONCAT" :-)

What typically happens is that *we* are using a function internally for something, someone stumbles upon it, blogs about it...and voila, everyone is using it *even though they know its undocumented*.


Here's how to go past 4000 with LISTAGG. Its in german, but the code is easy to follow

http://sql-plsql-de.blogspot.com.au/2014/01/sql-listagg-mit-clob-ausgabe-kein.html

A reader, April 25, 2018 - 4:00 pm UTC


thanks sir

A reader, September 25, 2020 - 11:46 am UTC


Connor McDonald
September 29, 2020 - 3:57 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.