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;

  • 9
    点赞
  • 18
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

山与海丫

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值