Oracle 19c版本后续不再支持wm_concat函数，推荐使用listagg改造业务SQL。
但是也可以重建wm_concat函数。
本次创建clob超长字符。和varchar2区别在于，varchar2只能处理4000字符以内的，如果连接超4000字符时，会报错
重建步骤：
解锁wmsys用户
conn / as sysdba alter user wmsys account unlock;
alter user wmsys identified by XXXXXXXX;
grant connect,resource,create public synonym to wmsys;
在wmsys下创建可用的wm_concat函数.
CREATE OR REPLACE NONEDITIONABLE TYPE WMSYS.WM_CONCAT_IMPL 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 CLOB) 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 ) ;
定义类型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 CLOB) 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; /
自定义行变列函数
当前 P1 VARCHAR2 数据类型未修改，后面如果再有问题可以修改下这个试试
CREATE OR REPLACE FUNCTION wmsys.wm_concat(P1 VARCHAR2) RETURN CLOB AGGREGATE USING 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;
