oralce函数wm_concat 替代办法

原帖地址：http://blog.csdn.net/pengdingxu10/article/details/46438567

oracle11g后 推荐使用listagg函数。

vm_concat

1.该函数不是oracle公开的系统函数，它的用户是wmsys,而不是sys或者system，oracle很有可能在版本升级或者补丁的时候取消或者修改这个函数甚至用户，这种变化oracle是不会公开的。所有可能会由于这个变化而导致异常。

2.大量使用这个函数也会导致临时表空间爆满，这是因为在10.2.0.5中，使用wmsys.wm_concat返回的结果格式是CLOB，CLOB占用的临时表空间只有在连接释放后才会释放，部分通过连接池连接数据库的长连接很有可能导致CLOB占用临时表空间不断累积增大，会导致临时表空间爆满的故障

3.如果是在程序中大量使用这个函数的话会引起enq:TT的锁，可能会导致某些对象被锁。

解决办法：

官方文档给了建议，可以参考该函数自己建立一个函数实现相同的行列转换功能。

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

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

create or replace FUNCTION en_concat(P1 VARCHAR2) RETURN VARCHAR2
  AGGREGATE USING en_concat_im;

然后运行测试：select en_concat(username) from dba_users;   ok可以实现行列转换。

但是我在运行的时候出了个问题，就是查询的表数据量比较大的时候会报：Oracle ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小这个错误

我当时不明白什么原因，后面百度了一下这个原因，看到了一篇文章：http://bbs.csdn.net/topics/360059765

楼主的问题：

使用wmsys.wm_concat多列合成一列遇到问题
ORA-22813: 操作数值超出系统的限制

往下看了之后对比了一下这个问题的解决办法，原理跟上面一样，也是自定义type来实现，但是有一个不同点的就是，上面的实现我标注的地方，返回的是varchar2类型的

在看看这篇文章里面的实现：

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

);

 

CREATE  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 ;

 

create  or  replace  FUNCTION  zh_concat(P1 VARCHAR2)

RETURN  clob AGGREGATE USING zh_concat_im ;

这个定义的的clob类型，所有这个函数可以实现，没有出现了Oracle ORA-06502:这个错误，顿时恍然啊。

至于clob这个类型跟varchar2各位就查资料看看了，但是目前我还是有点不明白，这样实现也是用clob这个类型，会不会也会临时表空间增大，目前没有测试过。