12C自定义wm_concat函数

   老的程序很多使用wm_concat函数，没有使用listagg,这种在迁移到12C时需要创建wm_concat才能使用（wm_concat是undocument函数，12c已经被废弃）。

--注意使用具体的用户名代替如下的WMSYS
--12c以上还要grant inherit privileges on user wmsys to PUBLIC;grant inherit privileges on user sys to PUBLIC;


CREATE OR REPLACE TYPE WMSYS.WM_CONCAT_10G_IMPL AUTHID CURRENT_USER AS OBJECT 
(
  CURR_STR VARCHAR2(32767),
  
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_10G_IMPL)
    RETURN NUMBER,
    
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_10G_IMPL,
                                       P1   IN     VARCHAR2) 
    RETURN NUMBER,
    
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_10G_IMPL,
                                         RETURNVALUE OUT VARCHAR2,
                                         FLAGS       IN NUMBER)
    RETURN NUMBER,
    
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_10G_IMPL,
                                     SCTX2 IN     WM_CONCAT_10G_IMPL)
    RETURN NUMBER
);
/


 CREATE OR REPLACE TYPE BODY WMSYS.WM_CONCAT_10G_IMPL IS
   STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_10G_IMPL)
     RETURN NUMBER IS
   BEGIN
     SCTX := WM_CONCAT_10G_IMPL(NULL);
     RETURN ODCICONST.SUCCESS;
   END;
 
   MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_10G_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_10G_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_10G_IMPL,                                      
                                      SCTX2    IN WM_CONCAT_10G_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 WMSYS.wm_concat(P1 VARCHAR2) RETURN VARCHAR2 
  PARALLEL_ENABLE AGGREGATE USING WMSYS.wm_concat_10g_impl;
/ 
 
-- 创建public同义词
create public synonym wm_concat for WMSYS.wm_concat;


-- 允许所有用户执行
grant execute on WMSYS.wm_concat to public;


-- 使用普通用户测试
SELECT wm_concat(owner) FROM DBA_OBJECTS WHERE ROWNUM<10;


--12c还需要授权
grant inherit privileges on user wmsys to PUBLIC;
grant inherit privileges on user sys to PUBLIC;

 
  建议使用listagg代替wm_concat,效率更佳，而且19c listagg支持distinct,具体见：
Oracle 19c New Features:LISTAGG函数功能增加DISTINCT