Oracle|19C升级WM_CONCAT函数失效

2024年 6月 30日 56.5k 0

1.1 问题背景

最近项目Oracle数据库升级由11g升到19C,在验证过程中发现wm_concat函数竟然失效了。

经过网上查询资料发现,wm_concat函数是oracle的非公开函数,在新版的oracle中不支持该函数。

Oracle|19C升级WM_CONCAT函数失效-1

1.2 解决方案

因该函数在代码中使用次数过多,在不改动代码前提下,选择重新创建该函数。建议后续在开发过程中不建议再使用该函数。

1.3 实施步骤

--首先使用dba账号登录oracle数据库(使用oracle root用户执行)

    sqlplus as sysdba

    Oracle|19C升级WM_CONCAT函数失效-2

    --解锁wmsys用户

      alter user wmsys account unlock;

      Oracle|19C升级WM_CONCAT函数失效-3

      --并为wmsys用户授权,可根据需要授权,不建议授权所有权限

        grant all privileges to wmsys;

        Oracle|19C升级WM_CONCAT函数失效-4

        --如果不知道wmsys用户的密码,可以修改其密码

          alter user wmsys identified by Pro_ut#19;

          Oracle|19C升级WM_CONCAT函数失效-5

          --使用wmsys用户登录数据库

            conn wmsys/Pro_ut#19

            Oracle|19C升级WM_CONCAT函数失效-6

            --在wmsys下创建可用的wm_concat函数,直接执行以下语句

            --定义类型

              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
              );
              /

              Oracle|19C升级WM_CONCAT函数失效-7

              --定义类型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 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;
                /

                Oracle|19C升级WM_CONCAT函数失效-8

                --自定义行变列函数:

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

                  Oracle|19C升级WM_CONCAT函数失效-9

                  --创建完成,给其创建同义词及授权,以供其他用户能正常使用。

                    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
                    /

                    Oracle|19C升级WM_CONCAT函数失效-10

                    1.4 方案验证

                    wm_concat函数可正常使用

                    Oracle|19C升级WM_CONCAT函数失效-11

                    end

                    Oracle|19C升级WM_CONCAT函数失效-12

                    相关文章

                    Oracle如何使用授予和撤销权限的语法和示例
                    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
                    下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
                    社区版oceanbase安装
                    Oracle 导出CSV工具-sqluldr2
                    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

                    发布评论