由于Oracle版本升级导致WM_CONCAT无效解决方案

最新推荐文章于 2024-03-14 16:29:15 发布

明眸-Sunshine仔

最新推荐文章于 2024-03-14 16:29:15 发布

阅读量911

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_41606459/article/details/103781006

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

Oracle版本过高(12c~)导致WM_CONCAT失效

一、sys 登录

从服务器登录（linux登录oacle）

su - oracle　　
//登录sqlplus　　　　 
sqlplus /nolog　　
//连接orcale　　 
conn xx/xx;(用户名/密码) 　（普通用户登录）　
或者 connect /as sysdba;（sys用户）

直接使用plsql登录sys

二、解锁sys用户

alter user sys account unlock;

三、创建包，包体，函数

以sys用户登录数据库，执行下面的命令

CREATE OR REPLACE TYPE 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
);

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 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 wm_concat(P1 VARCHAR2)
 
RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;

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