升级Oracle 19C后函数WM_CONCAT失效

文档课题：升级到Oracle 19C后函数WM_CONCAT失效.
1、场景重现
1.1、11g
操作如下：
> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/ Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
> conn scott/tiger
> create table test (testid number,
                test   varchar2(20),
               testdlj number);
> insert into test values (1,'cherry',123);
> insert into test values (1,'jack',456);
> insert into test values (2,'leo',789);
> commit;
> select * from test;

     TESTID TEST                      TESTDLJ
---------- -------------------- ----------
         1 cherry                      123
         1 jack                        456
         2 leo                         789
> select testid,wm_concat(test) as test,wm_concat(testdlj) as testdlj from test t group by testid

     TESTID TEST             TESTDLJ
---------- --------------- ---------------
         1 cherry,jack     123,456
         2 leo             789
> select testid,to_char(wm_concat(test)) as test,to_char(wm_concat(testdlj)) as testdlj from test t group by testid;

     TESTID TEST             TESTDLJ
---------- --------------- ---------------
         1 cherry,jack     123,456
         2 leo             789
小结：可以看到在11g中函数wm_concat正常使用.
1.2、19c
操作如下：
> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
> conn scott/tiger
> create table test (testid number,
                test   varchar2(20),
              testdlj number);
> insert into test values (1,'cherry',123);
> insert into test values (1,'jack',456);
> insert into test values (2,'leo',789);
> commit;
> select * from test;

     TESTID TEST                      TESTDLJ
---------- -------------------- ----------
         1 cherry                      123
         1 jack                        456
         2 leo                         789
>  select testid,wm_concat(test) as test,wm_concat(testdlj) as testdlj from test t group by testid;
 select testid,wm_concat(test) as test,wm_concat(testdlj) as testdlj from test t group by testid
                                    *
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier

小结：如上所示，在19c中使用wm_concat函数，出现报错ora-00904.该函数在oracle 19c中不再支持.
当数据库从11g升到19c后，若该函数在代码中使用频率较高，改动代码不现实，此时就需要重新建立该函数.
2、处理步骤
说明：在19c所在服务器处理.
2.1、wmsys用户
> conn / as sysdba
> alter user wmsys account unlock;
> alter user wmsys identified by Pro_ut#19;
> grant connect,resource,create public synonym to wmsys;
2.2、创建wm_concat
在wmsys下创建可用的wm_concat函数.
2.2.1、定义类型
> 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
)
;
/
2.2.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 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;
   /
2.2.3、行变列函数
自定义行变列函数
> CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2) RETURN VARCHAR2
      AGGREGATE USING WM_CONCAT_IMPL;
    /
2.2.4、同义词
–创建完行变列函数后给其创建同义词并授权，以供其他用户正常使用.
> 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;
3、查询验证
> conn scott/tiger
> select testid,wm_concat(test) as test,wm_concat(testdlj) as testdlj from test t group by testid

     TESTID TEST             TESTDLJ
---------- --------------- ----------
         1 cherry,jack     123,456
         2 leo             789
总结：wm_concat函数在19c中正常使用.
参考网址：https://blog.csdn.net/njdxtj/article/details/78625571