但行好事
莫论前程❤

ORA-00904:WM_CONCAT:标识符无效–手工新建函数

  • 手工创建该函数
  • 当放在一个文件里执行时,中间用“/”进行分割
  • 返回值为,需要用to_char函数进行字符串转换
    1、以sys用户创建包、包体、函数
CREATE OR REPLACE TYPE WM_CONCAT_IMPL AUTHID CURRENT_USER AS OBJECT
(
CURR_STR clob, 
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 clob,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);
/

2、创建类型body

CREATE OR REPLACE TYPE BODY 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 clob,
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;
/

c、创建函数

CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2) RETURN clob AGGREGATE USING WM_CONCAT_IMPL ;
/

d、创建同义词并授权 (略)

create public synonym WM_CONCAT_IMPL for sys.WM_CONCAT_IMPL
create public synonym wm_concat for sys.wm_concat
grant execute on WM_CONCAT_IMPL to public
grant execute on wm_concat to public

然后查看function状态,正常状态。至此问题解决。

SQL> select owner,object_name,object_type ,status from dba_objects where owner not in('SYS', 'SYSTEM') AND status != 'VALID' ;

总结:

从网上找资料了解到,11gr2和12C上已经摒弃了wm_concat函数,而应用在程序中确使用了该函数,导致程序出现错误,为了减轻程序员修改程序的工作量,只有通过手工创建个wm_concat函数,来临时解决该问题,但是注意,及时创建了该函数,在使用的过程中,也需要用to_char(wm_concat())方式,才能完全替代之前的应用。

赞(0) 打赏
未经允许不得转载:刘鹏博客 » ORA-00904:WM_CONCAT:标识符无效–手工新建函数
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏