在之前的博客我们看了Linux 系统中静默安装数据库软件和手工创建实例，如下：

Oracle 11g 单实例 中手工建库 示例
http://www.cndba.cn/dave/article/209http://www.cndba.cn/cndba/dave/article/4309http://www.cndba.cn/cndba/dave/article/4309

Oracle 11g 静默安装软件 创建监听 手册
https://www.cndba.cn/dave/article/1604http://www.cndba.cn/cndba/dave/article/4309http://www.cndba.cn/cndba/dave/article/4309

实例创建完后执行wmsys.wm_concat报如下错误：

SQL>select wmsys.wm_concat(t_name||':KHD'||p_id)  from (
  2        select b.p_id,c.t_name from kd_car a left join kd_product b  on a.c_product_id=b.p_id left join  kd_product_type c on b.p_type=c.t_id  where a.c_seller='dave@www.cndba.cn' and a.c_username='dave'
     )   ;  3
     select wmsys.wm_concat(t_name||':KHD'||p_id)  from (
            *
ERROR at line 1:
ORA-00904: "WMSYS"."WM_CONCAT": invalid identifier

出现这个错误是因为”WMSYS”.”WM_CONCAT”是Oracle 的内部函数，Oracle 不希望被外部使用，并且官方文档也从未说明过，在MOS 文章有解释这个问题，Oracle 的建议是11g 以后使用LISTAGG函数代替WM_CONCAT。

ORA-00904: “WMSYS”.”WM_CONCAT”: invalid identifier (Doc ID 2215183.1)http://www.cndba.cn/cndba/dave/article/4309

http://www.cndba.cn/cndba/dave/article/4309

CAUSE

WM_CONCAT is undocumented and unsupported by Oracle meaning it should not be used in production systems.

This function has been removed from Oracle 12c.
Since version 6.4.1 includes a database update to 12c this function can no longer be used.

SOLUTION
The Oracle PL/SQL WM_CONCAT function is used to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. In effect, it cross-tabulates a comma delimited list.

WM_CONCAT is gone in 12c.

Use the LISTAGG function instead, which can produce the same output as WM_CONCAT is both documented and supported by Oracle.

Basic Syntax:
LISTAGG(col_name_to_be_aggregated, ',') WITHIN GROUP (ORDER BY col)

但我们也可以手工执行如下三个脚本，来创建WMSYS的相关对象，执行成功后就可以运行wm_concat函数了。

SQL> @?/rdbms/admin/owmctab.plb
SQL> @?/rdbms/admin/owmaggrs.plb
SQL> @?/rdbms/admin/owmaggrb.plb

版权声明：本文为博主原创文章，未经博主允许不得转载。