Oracle19c使用wmsys.wm_concat时报错ORA-00904: “WMSYS“.“WM_CONCAT“: 标识符无效处理办法

0.前言

在数据库中创建视图时，提示ORA-00904: “WMSYS“.“WM_CONCAT“: 标识符无效。报错位置在于视图中使用了wmsys.wm_concat函数对数据字段进行拼接。

1.处理办法

1.1）设置实例

export ORACLE_SID=test1

1.2）首先使用dba账号登录oracle数据库

sqlplus / as sysdba

1.3）解锁wmsys用户

alter user wmsys account unlock;

1.4）为wmsys用户授权

（可根据需要授权，不建议授权所有权限）
grant all privileges to wmsys;

如果不知道wmsys用户的密码，可以修改其密码
alter user wmsys identified by haitaiinc;

如果是pdb数据库则需要切换。

切换到pdb
alter session set container=xxx;
切换或需要再次执行授权：
grant all privileges to wmsys;
（可根据需要授权，不建议授权所有权限）

1.5）使用wmsys用户登录数据库

conn wmsys/123456

如果是pdb ，则需要切换到pdb中，在pdb中创建函数。

1.6）创建函数

如果未切换到wmsys用户，则在创建过程中，函数及其他对象名字前面，需要加上用户wmsys。最好还是切换到wmsys用户下再执行以下步骤。

在wmsys下创建可用的wm_concat函数，直接执行以下语句
–定义类型
CREATE OR REPLACE TYPE wmsys.WM_CONCAT_IMPL AS OBJECT
(
CURR_STR VARCHAR2(32767),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);

–定义类型body:
CREATE OR REPLACE TYPE BODY wmsys.WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
SCTX := WM_CONCAT_IMPL(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ‘,’ || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
IF(SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ‘,’ || SCTX2.CURR_STR ;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;

–自定义行变列函数:
CREATE OR REPLACE FUNCTION wmsys.wm_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING wmsys.WM_CONCAT_IMPL ;

–创建完成，给其创建同义词及授权，以供其他用户能正常使用。
create public synonym WM_CONCAT_IMPL for wmsys.WM_CONCAT_IMPL;

create public synonym wm_concat for wmsys.wm_concat;

grant execute on WM_CONCAT_IMPL to public;

grant execute on wm_concat to public;

执行完成，再次执行 创建视图语句以及测试使用wm_concat函数时，可以正常使用。

2.总结

建议dba在部署完19c数据库时，就完成该函数的创建，以避免因应用程序中使用该函数而导致的报错。
如果是视图，可以考虑改用LISTAGG函数代替。
eg:
SELECT t.user_id , REPLACE(REPLACE(wm_concat(t.dept_code),‘,‘,’‘),’’,‘’) FROM usermgmt.user_x_duty t GROUP BY t.user_id ;
可以改写为：
SELECT t.user_id , REPLACE(LISTAGG(t.dept_code, ‘, ‘) WITHIN GROUP (ORDER BY user_id),’*,’,‘’) AS deptcodes
FROM usermgmt.user_x_duty t
GROUP BY user_id;