oracle Listagg函数和ORA-01489：字符串连接的结果太长

xqnpmsa8 于 2023-06-22 发布在 Oracle

关注(0)|答案(4)|浏览(329)

当我运行以下查询时：

Select
  tm.product_id,
  listagg(tm.book_id || '(' || tm.score || ')',',')
    within group (order by tm.product_id) as matches
from
  tl_product_match tm 
where
  tm.book_id is not null 
group by
  tm.product_id

Oracle返回以下错误：

ORA-01489: result of string concatenation is too long

我知道它失败的原因是listagg函数试图连接大于4000个字符的值，这是不支持的。
我已经看到了这里描述的替代示例-http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php，但它们都需要使用函数或过程。
有没有一种解决方案是纯SQL，而不必调用函数或存储过程，并且能够使用标准JDBC读取值？
我遇到的另一个困难是，我见过的大多数字符串聚合示例都显示了如何按原样读取值的示例。在我的例子中，我首先修改了值（即我正在聚合两列）。

oracle

来源：https://stackoverflow.com/questions/14864055/listagg-function-and-ora-01489-result-of-string-concatenation-is-too-long

4条答案

按热度按时间

1u4esq0p1#

如果你只需要前4,000个字符，LISTAGG函数从版本12.2开始就有一个选项，可以轻松地截断溢出的数据，添加一个elipsis，然后添加截断值的数量。

select owner, listagg(object_name, ',' on overflow truncate) within group (order by object_name) names
from all_objects
group by owner
order by owner;

OWNER   NAMES
-----  ------
SYS    ACCESS$,ACLMV$,[hundreds of names here],ALL_GOLDENGATE_RULES,...(47742)

赞(0）回复(0）举报 2023-06-22

mw3dktmi2#

你可以使用xml函数来完成它，它返回一个CLOB。JDBC应该很好。

select tm.product_id, 
       rtrim(extract(xmlagg(xmlelement(e, tm.book_id || '(' || tm.score || '),')), 
               '/E/text()').getclobval(), ',')
  from tl_product_match tm
 where tm.book_id is not null 
 group by tm.product_id;

例如：http：//sqlfiddle.com/#！4/083a2/1

赞(0）回复(0）举报 2023-06-22

wfauudbj3#

为什么不使用嵌套表？

set echo on;
set display on;
set linesize 200;

drop table testA;
create table testA
(
col1 number,
col2 varchar2(50)
);

drop table testB;
create table testB
(
col1 number,
col2 varchar2(50)
);

create or replace type t_vchar_tab as table of varchar2(50);

insert into testA values (1,'A');
insert into testA values (2,'B');

insert into testB values (1,'X');
insert into testB values (1,'Y');
insert into testB values (1,'Z');
commit;

-- select all related testB.col2 values in a nested table for each testA.col1 value
select a.col1, 
cast(multiset(select b.col2 from testB b where b.col1 = a.col1 order by b.col2) as t_vchar_tab) as testB_vals
from testA a;

-- test size > 4000
insert into testB
select 2 as col1, substr((object_name || object_type), 1, 50) as col2
from all_objects;
commit;

-- select all related testB.col2 values in a nested table for each testA.col1 value
select a.col1, 
cast(multiset(select b.col2 from testB b where b.col1 = a.col1 order by b.col2) as t_vchar_tab) as testB_vals
from testA a;

我不是javaMaven，但这已经有一段时间了，我相信java可以从嵌套表中提取值。而且，不需要在另一端标记一些分隔字符串。

赞(0）回复(0）举报 2023-06-22

afdcj2ne4#

我已经看到了这里描述的替代示例-http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php，但它们都需要使用函数或过程。
不，他们没有。向下滚动，你会看到几个不需要pl/sql的选项。

赞(0）回复(0）举报 2023-06-22