ora-01489字符串连接的结果过长解决⽅案
  如下代码，使⽤listagg进⾏分组拼接时，常常会报 ora-01489 错误，造成该报错的主要原因是：oracle对字符变量的长度限制，正常情况下，oracle定义的varchar2类型变量的长度不应超过4000字节，如有必要可转换为long 或clob类型。
  我之前遇到⼀次该报错，后来检查了下，是因为重复数据造成的，所以建议⼤家使⽤下⾯⽅法之前最好还是先看下数据。本⽂提供的所有⽅法总结于。
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;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。