签到成功

知道了

CNDBA社区CNDBA社区

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

2018-12-02 20:54 6544 0 原创 Oracle 18c
作者: dave

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

Oracle 12c 单实例 中手工建库 示例
https://www.cndba.cn/dave/article/226https://www.cndba.cn/cndba/dave/article/3187

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

查看官方文档,MOS上有一篇文档说明:https://www.cndba.cn/cndba/dave/article/3187

java.sql.SQLSyntaxErrorException: ORA-00904: “WMSYS”.”WM_CONCAT”: invalid identifier (文档 ID 2215183.1)

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.

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/cndba/dave/article/3187

WM_CONCAT is gone in 12c.

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

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/cndba/dave/article/3187

Basic Syntax:
LISTAGG(col_name_to_be_aggregated, ‘,’) WITHIN GROUP (ORDER BY col)
Def LISTAGG http://psoug.org/definition/LISTAGG.htm

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

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

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

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

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2256
    原创
  • 3
    翻译
  • 577
    转载
  • 191
    评论
  • 访问:7725542次
  • 积分:4330
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ