oracle 错误00904,Oracle 11g ORA-00904: "WMSYS"."WM_CONCAT"错误 解决方法

之前手工静默创建了一个数据库，测试的时候报ORA-00904: “WMSYS”.”WM_CONCAT”错误。

查看官方文档，MOS上有一篇文档说明：

https://www.cndba.cn/dave/article/3187

java.sql.SQLSyntaxErrorException: ORA-00904: “WMSYS”.”WM_CONCAT”: invalid identifier (文档 ID 2215183.1)https://www.cndba.cn/dave/article/3187https://www.cndba.cn/dave/article/3187

The database function WM_CONCAT is being used.

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.

https://www.cndba.cn/dave/article/3187

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. https://www.cndba.cn/dave/article/3187

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.https://www.cndba.cn/dave/article/3187

https://www.cndba.cn/dave/article/3187

Basic Syntax:

LISTAGG(col_name_to_be_aggregated, ‘,’) WITHIN GROUP (ORDER BY col)

Def LISTAGG http://psoug.org/definition/LISTAGG.htmhttps://www.cndba.cn/dave/article/3187https://www.cndba.cn/dave/article/3187

根据说明，函数”WMSYS”.”WM_CONCAT”在Oracle 12c中已经被移除了，我们这里的环境是11.2.0.4。以为是手工创建的数据库，所以也没有该函数，如果想继续使用，可以执行如下脚本，创建该函数：

SQL> @?/rdbms/admin/owmctab.plb;

SQL> @?/rdbms/admin/owmaggrs.plb;

SQL> @?/rdbms/admin/owmaggrb.plb;

SQL> @?/rdbms/admin/owmctab.plb;

Function created.

Grant succeeded.

PL/SQL procedure successfully completed.

Grant succeeded.

Procedure created.

Type created.

Type created.

PL/SQL procedure successfully completed.

Table created.

Index created.

Index created.

Table created.

Sequence created.

Table created.

Sequence created.

Table created.

1 row created.

Commit complete.

Sequence created.

Table created.

Table created.

Index created.

Table created.

1 row created.

Type created.

PL/SQL procedure successfully completed.

Type created.

PL/SQL procedure successfully completed.

Table created.

Sequence created.

Table created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

Commit complete.

Table created.

Table created.

Type created.

Table created.

Table altered.

Table created.

Sequence created.

Sequence created.

Sequence created.

Table created.

Table created.

Sequence created.

Table created.

1 row created.

1 row created.

Commit complete.

Table created.

Table created.

Index created.

1 row created.

Commit complete.

Index created.

Table created.

Table created.

1 row updated.

Commit complete.

Table altered.

PL/SQL procedure successfully completed.

Table altered.

Table altered.

Table altered.

1 row updated.

Commit complete.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

PL/SQL procedure successfully completed.

Index created.

Index created.

Index created.

0 rows deleted.

1 row created.

Commit complete.

Table created.

Index created.

1 row updated.

Commit complete.

Index dropped.

Index created.

Table created.

Table altered.

Table altered.

Table created.

Sequence created.

Index dropped.

Index created.

Table altered.

1 row updated.

Commit complete.

SQL> @?/rdbms/admin/owmaggrs.plb;

Type created.

PL/SQL procedure successfully completed.

Function created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Synonym created.

SQL> @?/rdbms/admin/owmaggrb.plb;

Type body created.

SQL> select account_status,wmsys.wm_concat(username) from dba_users group by account_status;

ACCOUNT_STATUS

----------------------------------------------------------------

WMSYS.WM_CONCAT(USERNAME)

--------------------------------------------------------------------------------

EXPIRED & LOCKED

OUTLN,ORACLE_OCM,DIP,WMSYS,DBSNMP,APPQOSSYS

OPEN

SYS,CAIWUMAO,CAIWUMAO_P,SYSTEM

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