12C新特性：WM_CONCAT函数的替代方式（2）

12C新特性：WM_CONCAT函数的替代方式（1）中提到了listagg长度不够，在部分情况下无法满足要求。在网络上找到了手工创建wm_concat函数的方法，现进行测试：

1、创建wm_concat函数

--解锁wmsys用户

alter user wmsys account unlock;

--并为wmsys用户授权，可根据需要授权，不建议授权所有权限
grant all privileges to wmsys;

--因为不知道wmsys用户的密码，将其修改
alter user wmsys identified by wmsys;

--使用wmsys用户登录数据库
conn wmsys/123456

--定义类型

CREATE OR REPLACE TYPE wm_concat_impl AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR    VARCHAR2(32767),
  CURR_STR_C  CLOB,
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 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,NULL) ;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF (CURR_STR_C IS NULL AND (CURR_STR IS NULL OR LENGTH(CURR_STR)<29950)) THEN
      IF(CURR_STR IS NOT NULL) THEN 
        CURR_STR := CURR_STR || ',' || P1;
      ELSE
        CURR_STR := P1;
      END IF;
    ELSE
      IF (CURR_STR_C IS NULL) THEN
        CURR_STR_C := CURR_STR ;
        CURR_STR := NULL ;
      END IF ;
      CURR_STR_C := CURR_STR_C || ',' || P1;
    END IF ;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS NUMBER) RETURN NUMBER IS
  BEGIN
    IF (CURR_STR IS NOT NULL) THEN
      RETURNVALUE := CURR_STR ;
    ELSE
      RETURNVALUE := CURR_STR_C ;
    END IF ;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 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 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;

--锁定wmsys用户
alter user wmsys account lock;

2、测试函数

SQL> SELECT wmsys.WM_CONCAT(TYPE) from v$parameter;

WMSYS.WM_CONCAT(TYPE)
--------------------------------------------------------------------------------
2,3,3,1,3,1,3,3,1,2,3,3,2,2,2,6,2,1,3,3,1,2,1,6,6,6,6,6,6,3,3,6,2,2,1,2,2,2,2,
2,

SQL> SET LONG 10000000 
SQL> /

WMSYS.WM_CONCAT(TYPE)
--------------------------------------------------------------------------------
2,3,3,1,3,1,3,3,1,2,3,3,2,2,2,6,2,1,3,3,1,2,1,6,6,6,6,6,6,3,3,6,2,2,1,2,2,2,2,
2,2,2,2,2,2,2,2,2,2,2,2,2,1,1,3,1,2,2,3,1,1,2,2,3,3,
2,2,3,3,3,3,2,1,2,2,2,1,2,1,3,3,2,1,6,6,6,2,2,2,3,3,
2,2,3,6,6,6,6,6,6,6,6,6,2,2,3,2,2,2,6,2,2,2,2,2,2,2,
2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,
2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,
2,2,2,2,2,1,2,2,3,2,2,3,3,2,2,3,3,2,3,6,3,3,3,3,3,1,
1,3,2,2,2,2,2,2,2,6,2,1,3,2,3,3,1,2,3,1,1,3,6,1,2,1,
3,1,3,3,1,2,2,2,3,2,3,3,2,6,6,2,2,1,2,2,2,2,2,2,2,2,
2,3,3,2,2,2,2,2,2,1,2,1,1,1,2,3,1,1,1,2,3,2,2,2,1,3,
3,3,2,2,3,3,3,3,3,1,2,2,2,2,2,1,3,2,2,1,2,2,1,3,2,2,

WMSYS.WM_CONCAT(TYPE)
--------------------------------------------------------------------------------
1,1,2,3,1,3,3,3,2,2,3,3,2,3,3,2,6,3,3,2,2,2,2,2,2,2,
2,2,1,3,3,1,3,3,3,2,2,2,2,2,2,3,3,1,1,2,2,2,2,2,3,4,
1,2,2,1,1,2,2,1,3,3,3,2,2,6,2,3,2,2,1,1,1,3,1,1,1,1,
2,2,1,1,1,3,2,1,1,1,2,1,1,2,1,1,2,2,2,3,1,1,6,6,2,1,
2,3,1,2,1,2,2,6,2,2,2,1,3,3,2,3,1,1,2,2,2,1,2,1,2,2,
1,2,3,3,1,2,2,2,2,6,3,2,2,2,3,2,2

测试成功，输出结果也是clob类型的。