WM_CONCAT Vs LISTAGG - ORA-00904: "WM_CONCAT": invalid identifier (Doc ID 2622063.1)

To BottomTo Bottom

In this Document

  Goal
  Solution
  References

 

APPLIES TO: 

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 18.5.0.0.0 [Release 10.1 to 18]
Information in this document applies to any platform.

GOAL

 From Oracle 12.1.0.1 onwards WM_CONCAT function is disabled. We need to use LISTAGG Function.

 EXAMPLE:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT WM_CONCAT(DISTINCT TYPE) from v$parameter;

WM_CONCAT(DISTINCTTYPE)
--------------------------------------------------------------------------------
1,2,3,4,6

SQL>

 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT WM_CONCAT(DISTINCT TYPE) from v$parameter;
SELECT WM_CONCAT(DISTINCT TYPE) from v$parameter
*
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier

SQL>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SOLUTION

 Why LISTAGG?

LISTAGG is superior to WM_CONCAT. It is fewer latches than wm_concat, latches are lightweight locks that impact the scalability, the more latches we do, the less the scalability is.

LISTAGG does not support the "DISTINCT" option. But it does exclude null values. So, you need to take care of the distinct values yourself before applying LISTAGG.

 EXAMPLE:

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t (
2 x int,
3 v varchar2(10)
4 );

Table created.

--
-- x=1 will be good stuff, x=2 will be bad stuff
--

SQL>
SQL> insert into t values (1,'Name1');

1 row created.

SQL> insert into t values (1,'Name2');

1 row created.

SQL> insert into t values (1,'Name3');

1 row created.

SQL>
SQL>

SQL> insert into t values (2,'Name1');

1 row created.

SQL> insert into t values (2,'Name2');

1 row created.

SQL> insert into t values (2,'Name1');
1 row created.

SQL> insert into t values (2,'Name2');

1 row created.

SQL> insert into t values (2,'Name4');

1 row created.

SQL>
SQL> select listagg(v,',') within group ( order by v )
2 from t
3 where x = 1;
LISTAGG(V,',')WITHINGROUP(ORDERBYV)
----------------------------------------------------------------------------------------------------------------------------------
Name1,Name3,Name2
1 row selected.
SQL>
SQL>
SQL> select listagg(v,',') within group ( order by v )
2 from t
3 where x = 2;
LISTAGG(V,',')WITHINGROUP(ORDERBYV)
----------------------------------------------------------------------------------------------------------------------------------
Name4,Name1,Name1,Name2,Name2

1 row selected.

SQL>
SQL> select listagg(v,',') within group ( order by v )
2 from ( select distinct v from t where x = 2 );
LISTAGG(V,',')WITHINGROUP(ORDERBYV)
----------------------------------------------------------------------------------------------------------------------------------
Name4,Name1,Name2

1 row selected.
SQL>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 More Information:

From the doc: Oracle Database 12.2 New Features

"Enhanced LISTAGG Functionality
LISTAGG aggregates the values of a column by concatenating them into a single string. New functionality has been added for managing situations where the length of the concatenated string is too long.

Developers can now control the process for managing overflowing LISTAGG aggregates. This increases the productivity and flexibility of this aggregation function."

  

REFERENCES