Why does 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',',')))) 

which returns:

But when I do this:

 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 an empty result ... what am I doing wrong?

Answer 1

You should avoid the wm_concat function because it is undocumented and detected as a workaround in Oracle 8i times.

Since the old method with a custom aggregate function discovered by Tom Kyte here , there are several new workarounds shown in the examples below.

All of them are reproduced in this SQL script .

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 with 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 with 10g:

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

PS I didn’t know exactly in which version of Oracle sys_connect_by_path and xmlagg were introduced, but both work well on 10.2.0.4.0

Answer 2

If you're on 11g, try LISTAGG instead of wm_concat for starters.

Answer 3

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

Any application that relied on the wm_concat function will not work after upgrading to 12c . Since it has been removed from the latest version 12c. See Why not use the 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 the error message < Invalid identifier :

 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, it makes no sense to rely on an undocumented function that is no longer available in recent versions.

For alternatives, see String Aggregation Methods in Oracle

Answer 4

You don't seem to be doing anything wrong. Using a dummy table to return the data that 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 marked the question as [11g]; as @beherenow said, if you can use supported lisgagg over unsupported wm_concat , although it is 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 (only for listagg , since it does not support wm_concat - maybe your instance isn’t there either, but should this be an error?)