1

I have used wmsys.wm_concat in a SQL statement in the SQL Developer and it works perfectly fine. But now I am wondering whether wmsys functions are available for every user operating on the database. Can every user call functions of wmsys?
So if I give the code to someone else are there going to be problems executing wmsys.wm_concat?

Thanks.

Thomas Koch
  • 21
  • 2
  • wm_concat is an undocumented function, so it would be better avoiding it; consider that it does not exist in 12c; what oracle version are you in? – Aleksej Mar 31 '16 at 12:49
  • 11g, but I'll look for another solution of my problem then. Better now than in 6 months. Thanks. – Thomas Koch Mar 31 '16 at 13:07
  • 1
    Do not use `WM_CONCAT` since it is an **undocumented feature** and it has been **removed** from the latest **12c version**. Any application which has had been relying on `wm_concat` function will not work once upgraded to `12c`. See [**Why not use WM_CONCAT function in Oracle?**](https://lalitkumarb.wordpress.com/2015/04/29/why-not-use-wm_concat-function-in-oracle/) – Lalit Kumar B Mar 31 '16 at 13:15

2 Answers2

4

In Oracle 11gR2 you can use LISTAGG to aggregate strings.

WMSYS.WM_CONCAT is an undocumented feature that is not available in all Oracle installations (and is not available at all in Oracle 12c).

MT0
  • 143,790
  • 11
  • 59
  • 117
  • I almost always forget my own Oracle blog, good to see you found it somehow +1 – Lalit Kumar B Mar 31 '16 at 13:17
  • Thank you a lot.LISTAGG works fine! One last question, is LISTAGG only Oracle version 11g and newer? – Thomas Koch Mar 31 '16 at 13:55
  • @ThomasKoch Yes, `LISTAGG` was introduced with **Oracle 11gR2**. If you are on a previous release, see http://stackoverflow.com/a/32525152/3989608 – Lalit Kumar B Mar 31 '16 at 14:07
  • One caveat - LISTAGG is a VARCHAR2 return type and so, in SQL, errors out if your list exceeds 4K characters. If that is a worry there are also Tom Kyte's custom analytic STRAGG option, or XMLAGG. – Michael Broughton Mar 31 '16 at 14:07
  • @MichaelBroughton Yes, `XMLAGG` is the savior in that case http://stackoverflow.com/a/29776515/3989608 – Lalit Kumar B Mar 31 '16 at 14:20
0

There is a decision User defined aggregares

This approach permit you use aggregates and concatenation without undocumented functions from wmsys

Dart XKey
  • 54
  • 3