Oracle 拼接结果过长,ora-01489 字符串连接的结果过长 解决方案

如下代码，使用listagg进行分组拼接时，常常会报 ora-01489 错误，造成该报错的主要原因是：oracle对字符变量的长度限制，正常情况下，oracle定义的varchar2类型变量的长度不应超过4000字节，如有必要可转换为long 或clob类型。

我之前遇到一次该报错，后来检查了下，是因为重复数据造成的，所以建议大家使用下面方法之前最好还是先看下数据。本文提供的所有方法总结于 ora-01489错误解决方案。

create table lu_meseno_temp as

select

MDSENO,

LISTAGG(to_char(MECODE), ',') WITHIN GROUP(ORDER BY MECODE) AS pjMECODE

from

lu_yb_sbda_md_temp

group by

MDSENO

解决方案：

方法一：自定义连接函数

-- 定义 tab_varchar2 数据类型

CREATE TYPE tab_varchar2 AS TABLE OF VARCHAR2(4000);

-- 新建 concat_array 函数

CREATE OR REPLACE FUNCTION concat_array(p tab_varchar2) RETURN CLOB IS

l_result CLOB;

BEGIN

FOR cc IN (SELECT column_value FROM TABLE(p) ORDER BY column_value) LOOP

l_result := l_result ||' '|| cc.column_value;

END LOOP;

return l_result;

END;

-- 分组拼接

SELECT

item,

concat_array(CAST(COLLECT(attribute) AS tab_varchar2)) attributes

FROM

tb

GROUP BY

item;

如果希望对上述结果进行排序，可以嵌套一层 order by 前4000字符。

SELECT

*

FROM

(

SELECT

item,

concat_array(CAST(collect(attribute) AS tab_varchar2)) attributes

FROM

tb

GROUP BY

item

)

order by

-- 表示截取长度4000，起始位置1

dbms_lob.substr(attributes, 4000, 1);

方法二：

with

ItemAttribute as (

select

'name'||level name,

mod(level,3) itemid

from dual

connect by level < 2000

),

ItemAttributeGrouped as (

select

xmlagg(xmlparse(content name||' ' wellformed) order by name).getclobval() attributes,

itemid

from ItemAttribute

group by itemid

)

select

itemid,

attributes,

dbms_lob.substr(attributes,4000,1) sortkey

from ItemAttributeGrouped

order by dbms_lob.substr(attributes,4000,1);

rtrim(xmlagg(XMLELEMENT(e, t.id, ',').EXTRACT('//text()')).getclobval(),',')

其他方法：

SELECT itemId, name

FROM (

SELECT itemId, name, min(dr) over (partition by itemId) as dr

FROM (

SELECT itemId, name,

dense_rank() over (order by name, name1, name2, name3, name4) as dr

FROM (

SELECT Item.itemId,

Attribute.name,

LEAD(Attribute.name, 1)

OVER (PARTITION BY Item.itemId

ORDER BY Attribute.name) AS name1,

LEAD(Attribute.name, 2)

OVER (PARTITION BY Item.itemId

ORDER BY Attribute.name) AS name2,

LEAD(Attribute.name, 3)

OVER (PARTITION BY Item.itemId

ORDER BY Attribute.name) AS name3,

LEAD(Attribute.name, 4)

OVER (PARTITION BY Item.itemId

ORDER BY Attribute.name) AS name4

FROM Item

JOIN ItemAttribute

ON ItemAttribute.itemId = Item.itemId

JOIN Attribute

ON Attribute.attributeId = ItemAttribute.attributeId

)

)

)

ORDER BY dr, name;