2023-02-06 oracle12使用wm_concat()函数

从网上找到代码有点问题：

1. 4000字符串超长
2. 不支持DISTINCT，没有去重

直接解密Oracle11G的原始程序，执行OK

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

CREATE OR REPLACE TYPE BODY wm_concat_new IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT wm_concat_new) RETURN NUMBER IS
  BEGIN
    SCTX := wm_concat_new(NULL,NULL) ;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT wm_concat_new, 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_new, 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_new, SCTX2 wm_concat_new) 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_new ;

解密工具：
一款比较好用的工具DfUnWraper
下载地址：
http://files.cnblogs.com/dint/DfUnWraper.zip