listagg函数 ORA-01489: result of string concatenation is too long的解决办法

概述

listagg 函数是Oracle 11g推出的一个分组函数,可以将字符串按分组连接起来.

SQL> select deptno ,listagg(ename,'->') within group ( order by ename)

2 from scott.emp

3 group by deptno;

DEPTNO LISTAGG(ENAME,'->')WITHINGROUP

------ --------------------------------------------------------------------------------

10 CLARK->KING->MILLER

20 ADAMS->FORD->JONES->SCOTT->SMITH

30 ALLEN->BLAKE->JAMES->MARTIN->TURNER->WARD

需要注意的是listagg函数只能返回VARCHAR2和rac类型的值.

The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2.(以上内容摘自官方文档)

varcahr2 最多可以容纳4000个字符 具体内容参见:Oracle数据类型限制

#ORA-01489: result of string concatenation is too long

如果我们运行下面的程序则会触发ORA-01489错误

SQL> select listagg(rownum,'->') within group ( order by rownum)

2 from dual

3 connect by level <=1000 ;

select listagg(rownum,'->') within group ( order by rownum)

from dual

connect by level <=1000

ORA-01489: result of string concatenation is too long

我们来看一下ORA-01489这个报错的详细信息

[oracle@server ~]$ oerr ora 1489

01489, 00000, "result of string concatenation is too long"

// *Cause: String concatenation result is more than the maximum size.

原因:字符串连接结果超出了最大尺寸

// *Action: Make sure that the result is less than the maximum size.

而Oracle中能存储字符串最多的数据类型为CLOB.所以需要我们自写函数解这个问题

自定义聚集函数

对象声明部分

create or replace type dao_connect_impl as object

(

RES clob,

static function ODCIAggregateInitialize(sctx IN OUT dao_connect_impl)

return number,

member function ODCIAggregateIterate(self IN OUT dao_connect_impl,

value IN VARCHAR2) return number,

member function ODCIAggregateTerminate(self IN dao_connect_impl,

returnValue OUT clob, flags IN NUMBER) return number,

member function ODCIAggregateMerge(self IN OUT dao_connect_impl,

ctx2 IN dao_connect_impl) return number

)

对象实现部分

create or replace type body dao_connect_impl is

static function ODCIAggregateInitialize(sctx IN OUT dao_connect_impl)

return number is

begin

sctx := dao_connect_impl(null);

return ODCIConst.Success;

end;

member function ODCIAggregateIterate(self IN OUT dao_connect_impl, value IN VARCHAR2) return number is

begin

SELF.RES :=SELF.RES||','||VALUE ;

return ODCIConst.Success;

end;

member function ODCIAggregateTerminate(self IN dao_connect_impl,

returnValue OUT clob, flags IN number) return number is

begin

returnValue := ltrim(self.RES,',');

return ODCIConst.Success;

end;

member function ODCIAggregateMerge(self IN OUT dao_connect_impl, ctx2 IN dao_connect