Oracle 10g 解决wm_concat函数超过4000报错问题

转：http://blog.itpub.net/28998293/viewspace-1353103/
Oracle 10g版本的wm_concat为varchar2（4000），在使用wm_concat函数时，在字符串长度超过4000后报错，所以自定义一个类似的聚集函数，在内部使用CLOB处理，避免超长问题。
Oracle 11g版本的vm_concat返回类型为clob。
源码如下：

--先以sys用户授权：
grant execute on dbms_lob to xxx;

CREATE OR REPLACE TYPE typ_concat_clob AS OBJECT
(
  concat_str CLOB,
  splitstr CLOB,
  --ODCIAggregateInitialize做一些初始化操作
  STATIC FUNCTION ODCIAggregateInitialize(cs_ctx In Out typ_concat_clob) return number,
  --ODCIAggregateIterate是主要的处理逻辑所在，这里定义一个迭代操作
  member function ODCIAggregateIterate(self In Out typ_concat_clob,srcvalue in VARCHAR2) return number,
  -- ODCIAggregateMerge是一个合并函数，如果在使用时指定了partition enabled，就必须定义此函数，用来把并行处理的结果进行合并
  member function ODCIAggregateMerge(self In Out typ_concat_clob,ctx2 In Out typ_concat_clob) return number,
  --ODCIAggregateTerminate是一个终止函数，顾名思义，在这个函数中对结果做最后处理并返回
  member function ODCIAggregateTerminate(self In Out typ_concat_clob,returnValue Out CLOB,flags IN NUMBER) return number
)

CREATE OR REPLACE TYPE BODY typ_concat_clob IS
  STATIC FUNCTION ODCIAggregateInitialize(cs_ctx In Out typ_concat_clob) return NUMBER
  IS
  BEGIN
    cs_ctx := typ_concat_clob(to_clob(','),to_clob(','));
    RETURN ODCICONST.SUCCESS;
  END;

  member function ODCIAggregateIterate(self In Out typ_concat_clob,srcvalue IN VARCHAR2) return NUMBER
  IS
  BEGIN
    dbms_lob.append(self.concat_str,to_clob(srcvalue));
    dbms_lob.append(self.concat_str,self.splitstr);
    RETURN ODCICONST.SUCCESS;
  END;

  member function ODCIAggregateMerge(self In Out typ_concat_clob,ctx2 In Out typ_concat_clob) return NUMBER
  IS
  BEGIN
    IF ctx2 IS NOT NULL THEN
      dbms_lob.append(self.concat_str,ctx2.concat_str);
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;

  member function ODCIAggregateTerminate(self In Out typ_concat_clob,returnValue Out CLOB,flags IN NUMBER) return NUMBER
  IS
  BEGIN
    dbms_lob.trim(self.concat_str,dbms_lob.getlength(self.concat_str)-1);
    self.concat_str := dbms_lob.substr(self.concat_str,dbms_lob.getlength(self.concat_str)-1,2);
    returnValue := self.concat_str;
    RETURN ODCICONST.SUCCESS;
  END;
END;
/

CREATE OR REPLACE FUNCTION fun_concat_clob(in_str VARCHAR2) RETURN CLOB
  AGGREGATE USING typ_concat_clob;
/

使用和wm_concat一样，不同的是内部处理和返回值都是CLOB类型的

SELECT fun_concat_clob(name) FROM students GROUP BY class;