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

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

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

?

1 2 3 4 5 6 7 8

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

　　解决方案：

　　方法一：自定义连接函数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

-- 定义 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字符。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

　　

方法二：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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

　　文档 oracle listagg函数字符串链接的结果过长，给出的解决方案为：

?

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

　　其他方法：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

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;