之前手工静默创建了一个数据库,测试的时候报ORA-00904: “WMSYS”.”WM_CONCAT”错误。
Oracle 12c 单实例 中手工建库 示例
https://www.cndba.cn/dave/article/226
查看官方文档,MOS上有一篇文档说明:
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.
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)
Def LISTAGG http://psoug.org/definition/LISTAGG.htm
根据说明,函数”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
版权声明:本文为博主原创文章,未经博主允许不得转载。
- 上一篇:多言必失,失在20处
- 下一篇:CentOS 7.6 (1810) 正式发布