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

　　文档 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;

巴特西

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

最新文章

热门文章

巴特西

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

最新文章

热门文章

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