String aggregation related questions are frequently asked questions and every now and then someone provides a solution using WM_CONCAT function. And it really annoys me to repeat the same thing every time.
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.
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.
SQL>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 identifierTherefore, there is no point relying on an undocumented feature which is no more made available in latest versions.
Lalit Kumar B 
Great, so we never should use any useful feature that might get removed in the future? XD
Nobody could stop you from using it, I believe you wouldn’t like to use it in production as it is an undocumented feature.
Pingback: Why does the wm_concat not work here?