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;