RETURN ODCICONST.SUCCESS; END;END;/函数：create or replace FUNCTION zh_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE U
1.
select postgresql('emp','ename') from dual
2.sys_connect_by_path
3.create or replace TYPE zh_concat_im
AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
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 VARCHAR2,
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 VARCHAR2,
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 VARCHAR2 AGGREGATE USING zh_concat_im ;
修改标红部门，，可调整字段间分隔符
本文原创发布php中文网，转载请注明出处，感谢您的尊重！

oracle数据库wm_concat有长度限制，如果长度超长会显示如下提示：



例如原始SQL:

select rcid, wm_concat(column_comment) as des from dm_s_resource_catalog_metadata where rcid=‘d6b3d14a17314cdbbe750a57428c4abc’ group by rcid
替代方案：

select XMLAGG(XMLELEMENT(E, column_comment || ‘,’)).EXTRACT(’//text()’).getclobval() des from dm_s_resource_catalog_metadata where rcid=‘d6b3d14a17314cdbbe750a57428c4abc’

原帖地址：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这个类型，会不会也会临时表空间增大，目前没有测试过。

个人技术网站 欢迎关注

原文链接：https://blog.csdn.net/m0_37548754/article/details/78553037

wm_concat函数是oracle拼接字符串常用函数，但是这函数并不是oracle官方推荐的，估计oracle在后续更新中会悄无声息的去除，在oracle 11g之前可以使用wm_concat，但是如果拼接字符串超过3000就会报错

Oracle ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小这个错误

解决办法：

官方建议自己建立一个函数实现相同的行列转换功能

第一步执行：

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 ;

实际使用例子：仅供参考



直接使用zh_concat替换wm_concat函数即可 不用动原有sql结构，

在此作为事件记录