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."

ITPub博客

【LISTAGG】 ORA-00904: "WM_CONCAT": invalid identifier (Doc

APPLIES TO:

GOAL

SOLUTION

最新文章