ora

#ora| 来源: 网络整理| 查看: 265

　　如下代码，使用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);

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

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;

【本文地址】

公司简介

联系我们