Why does the wm_concat not work here?

Question

I have this query :

(SELECT OBJECT_ID from cr_object_group_entries_vw where object_group_id IN
    (SELECT ITEM FROM TABLE(CR_FN_SPLIT_STRING('28,56',','))))

that returns :

But when I do :

SELECT wm_concat(object_id) FROM
    (SELECT OBJECT_ID from cr_object_group_entries_vw where object_group_id IN
        (SELECT ITEM FROM TABLE(CR_FN_SPLIT_STRING('28,56',','))))

I get a blank result... what am I doing wrong?

Answer 1

You must avoid wm_concat function because it is undocumented and discovered as workaround at Oracle 8i times.

Since times of old method with custom aggregate function as discovered by Tom Kyte here there are some new workarounds, showed at examples below.

All of them reproduced in this SQL Fiddle.

Workaround 1 - LISTAGG function, works in 11g:

select listagg(object_id,',') within group (order by rownum) id_string
from cr_object_group_entries_vw

Workaround 2 - SYS_CONNECT_BY_PATH, works since 10g:

select id_string from (
  select rn, substr(sys_connect_by_path(object_id, ','),2) id_string
  from (select object_id, rownum rn from cr_object_group_entries_vw)
  start with rn = 1
  connect by prior rn + 1 = rn
  order by rn desc
)
where rownum = 1

Workaround 3 - XMLAGG, works since 10g:

select replace(
         replace(
           replace(
             xmlagg(xmlelement("x",object_id)).getStringVal(),
             '</x><x>',
             ','
           ),
           '<x>',
           ''
         ),
         '</x>',
         ''
       ) id_string
from cr_object_group_entries_vw

P.S. I didn't know exactly in which Oracle versions sys_connect_by_path and xmlagg was introduced, but both works well on 10.2.0.4.0

Answer 2

In case you're on 11g try LISTAGG instead of wm_concat for starters.

Answer 3

You don't appear to be doing anything wrong. With a dummy table function to return the data you showed, wm_concat worked for me:

select wm_concat(object_id) from
    (select object_id from cr_object_group_entries_vw where object_group_id in
        (select item from table(cr_fn_split_string('28,56',','))))
/

WM_CONCAT(OBJECT_ID)                                                           
--------------------------------------------------------------------------------
36,1,11,121,13,14,17,18,2,24,3,32,33,34,35,36,37,38,39,40,42,43,44,6,7,8,81      

You've tagged the question as [11g]; as @beherenow said, if you can you should use the supported lisgagg over the unsupported wm_concat, though it's only available from 11gR2 I think:

select listagg(object_id, ',') within group (order by object_id)
from cr_object_group_entries_vw
where object_group_id in
    (select item from table(cr_fn_split_string('28,56',',')))
/

LISTAGG(OBJECT_ID,',')WITHINGROUP(ORDERBYOBJECT_ID)
---------------------------------------------------------------------------
1,11,121,13,14,17,18,2,24,3,32,33,34,35,36,36,37,38,39,40,42,43,44,6,7,8,81

SQL Fiddle (for listagg only, since it doesn't support wm_concat - maybe your instance doesn't either, but then it should error?)

Answer 4

I just saw this post regarding wm_concat and thought to share some information.

Any application which has had been relying on wm_concat function will not work once upgraded to 12c. Since, it has been removed from the latest 12c version. See Why not use WM_CONCAT function in Oracle?

SQL> select banner from v$version where rownum = 1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> SELECT object_name
  2  FROM dba_objects
  3  WHERE owner='WMSYS'
  4  AND object_name LIKE 'WM\_%' ESCAPE '\';

OBJECT_NAME
----------------------------------------------------------------------------
WM_REPLICATION_INFO
WM_RDIFF
WM_PERIOD
WM_PERIOD
WM_OVERLAPS
WM_MEETS
WM_LESSTHAN
WM_LDIFF
WM_INTERSECTION
WM_INSTALLATION
WM_GREATERTHAN
WM_EVENTS_INFO
WM_ERROR
WM_ERROR
WM_EQUALS
WM_DDL_UTIL
WM_DDL_UTIL
WM_CONTAINS
WM_COMPRESS_BATCH_SIZES
WM_COMPRESSIBLE_TABLES

20 rows selected.

You will receive an “invalid identifier” error:

SQL> SELECT banner FROM v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SQL> SELECT deptno, wm_concat(ename) FROM emp;
SELECT deptno, wm_concat(ename) FROM emp
               *
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier

Therefore, there is no point relying on an undocumented feature which is no more made available in latest versions.

For alternate solutions, please see Oracle String Aggregation Techniques