Oracle 自定义wmsys.wm_concat 避免长度限制

Question

Oracle 自定义wmsys.wm_concat 避免长度限制 网上搜索到这段类似代码但是长度过程还是不行，经过我的修改，亲测可以。
CREATE OR REPLACE TYPE ZH_CONCAT_IM  
AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR CLOB,
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT ZH_CONCAT_IM) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT ZH_CONCAT_IM,  
  P1 IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN ZH_CONCAT_IM,
  RETURNVALUE OUT CLOB,
  FLAGS IN NUMBER)
  RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT ZH_CONCAT_IM,  
  SCTX2 IN ZH_CONCAT_IM) RETURN NUMBER
)
GOCREATE OR REPLACE TYPE BODY ZH_CONCAT_IM
IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT ZH_CONCAT_IM)
  RETURN NUMBER  
  IS
  BEGIN
  SCTX := ZH_CONCAT_IM(NULL) ;
  RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT ZH_CONCAT_IM,
  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 ZH_CONCAT_IM,
  RETURNVALUE OUT CLOB,
  FLAGS IN NUMBER)
  RETURN NUMBER  
  IS
  BEGIN
  RETURNVALUE := CURR_STR ;
  RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT ZH_CONCAT_IM,  
  SCTX2 IN ZH_CONCAT_IM)  
  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;
GO
CREATE OR REPLACE FUNCTION ZH_CONCAT(P1 VARCHAR2)  
RETURN CLOB AGGREGATE USING ZH_CONCAT_IM ;

Answer 1

自己写自定义函数太麻烦,用xmlagg就可以了,支持返回clob 和string类型
select rtrim(xmlagg(xmlparse(content  l||',' wellformed)) .getclobval() ,',') /* .getstringval()*/  from 
(select  level as l from dual connect by level<=5000)

Answer 2

oracle11g和10g返回的类型不同

Answer 3

clob 是以性能为代价的，这个从10205和11.2开始就是这个类型了。该函数为oracle内部函数，不建议使用，在12c中已经不提供该函数，关键建议listagg

Answer 4

这种写法很普遍了，网上都有。
自己以前总结过这几种用法，欢迎大家指正。
假如这种写法的名字是clob_concat,原写法返回varchar2的名字是vc_concat。
listagg限制太多，只有在11g才有且只能返回varchar2类型。vc_concat和clob_concat这两个内部函数不能内部排序，且内部聚合是混乱的。比如
select wm_concat(col1) col3,wm_concat(col2) col4 from tab;
返回的col3和col4里的聚合数据未必是一一对应的，这点不注意很容易出错。
关于效率对比：
分情况：
1、需要返回varchar2，不需排序的效率对比：【vc_concat】>【listagg】>【xmlagg】
2、需要返回clob，不需要排序的效率对比：【clob_concat】>【xmlagg】
3、需要返回varchar2，且需要排序的效率对比：【listagg】>【xmlagg】>【vc_concat网上流传的利用分析函数排序后取最大值的方法】
4、需要返回clob，且需要排序的效率对比：【xmlagg】>【clob_concat网上流传的利用分析函数排序后取最大值的方法】
总结：
不涉及排序或者对应的话优先选择vc_concat或者clob_concat，有排序的情况优先选择listagg，再考虑用2楼xmlagg的方法。