Why does the wm_concat not work here?

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?

Solution 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

Solution 2:

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