oracle Listagg 函数和 ORA-01489：字符串连接的结果太长
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14864055/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Listagg function and ORA-01489: result of string concatenation is too long
提问by ziggy
When i run the following query:
当我运行以下查询时：
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 returns the following error:
Oracle 返回以下错误：
ORA-01489: result of string concatenation is too long
I know that the reason it is failing is that the listagg function is trying to concatenate a the values which are greater than 4000 characters which is not supported.
我知道它失败的原因是 listagg 函数试图连接一个不支持的大于 4000 个字符的值。
I have seen the alternative example described here - http://www.oracle-base.com/articles/misc/string-aggregation-techniques.phpbut they all require the use of functions or procedure.
我已经看到这里描述的替代示例 - http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php但它们都需要使用函数或过程。
Is there a solution that is pure SQL without having to call a function or stored procedure and being able to read the value using standard JDBC?
是否有纯 SQL 的解决方案，而无需调用函数或存储过程，并且能够使用标准 JDBC 读取值？
The other difficulty i have is that most string aggregation examples i have seen shows examples with how to read the value as is. In my example about i am modifying the value first (i.e. i am aggregating two columns).
我遇到的另一个困难是，我见过的大多数字符串聚合示例都显示了如何按原样读取值的示例。在我关于我首先修改值的示例中（即我正在聚合两列）。
回答by DazzaL
you can use xml functions to do it which return a CLOB. JDBC should be just fine with that.
您可以使用返回 CLOB 的 xml 函数来执行此操作。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;
回答by tbone
Why not use nested tables?
为什么不使用嵌套表？
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;
I'm no java expert, but this has been around for some time and I'm sure java can pull the values out of the nested table. And, no need to tokenize some delimited string on the other end.
我不是 Java 专家，但这已经有一段时间了，我确信 Java 可以从嵌套表中提取值。并且，不需要在另一端标记一些分隔的字符串。
回答by ivanatpr
I have seen the alternative example described here - http://www.oracle-base.com/articles/misc/string-aggregation-techniques.phpbut they all require the use of functions or procedure.
我已经看到这里描述的替代示例 - http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php但它们都需要使用函数或过程。
No they don't. Scroll down and you'll see several options that don't require pl/sql.
不，他们没有。向下滚动，您将看到几个不需要 pl/sql 的选项。
