问题描述
我使用的是Oracle 10g，并具有以下表结构:ID，段落
I'm on Oracle 10g and have the following table structure:id,paragraph
我想按ID分组并连接段落.每个段落可能超过1500个字符.
I want to group by id and concatenate the paragraphs. Each paragraph maybe 1500 characters or more.
当我尝试wm_concat函数时，它抱怨字符串缓冲区太小.实际上，我在Oracle网站上尝试了许多示例，但所有示例均因字符串缓冲区太小而失败.
When I try the wm_concat function, it complains that the string buffer is too small. I actually tried many of the examples on Oracle's website and they all fail with the error the string buffer is too small.
select id, wm_concat(paragraph) from paragraphs group by id
我该如何解决?
推荐答案
因此，我猜测错误是 ORA-06502 ，我可以看到您可能会认为在这种情况下这不适用于您.
So, I'm guessing the error is ORA-06502 and I can see how you might think that this doesn't apply to you in this situation.
但是，这是wm_concat的错误.这是一个函数，并受PL \ SQL中Oracle的最大varchar长度(32,767和标准SQL中的4,000)的约束.不幸的是，由于wm_concat的工作方式或函数中的任何较低约束，或者由于您在select中使用它，您都无法获得接近上限的任何信息.
However, this is the fault of wm_concat. This is a function and is constrained by Oracle's maximum varchar length in PL\SQL of 32,767 and 4,000 in standard SQL. Unfortunately, I assume, because of the way that wm_concat works or because of any lower constraints within the function or because you're using it in a select you can't get anywhere near the upper limit.
还有另一个选项， stragg ，Tom Kyte的字符串聚合函数.如果我们看一下两者之间的以下比较，您会发现它们的性能几乎相同，并且两者的长度上限均为4,000，即标准SQL最大值. stragg稍快一些，可能是由于缓存.
There is another option, stragg, Tom Kyte's string aggregate function. If we look at the following comparison between the two you'll see that they perform almost identically and that the limit of both is a length of around 4,000, i.e. the standard SQL maximum. stragg is slightly faster, probably due to caching.
SQL> set serveroutput on
SQL>
SQL> create table tmp_test ( a varchar2(30) );
Table created.
SQL> insert into tmp_test
2 select object_name
3 from all_objects
4 ;
81219 rows created.
SQL> commit ;
Commit complete.
SQL>
SQL> declare
2
3 i integer := 1;
4 k number(10);
5 v_stragg varchar2(32767);
6 v_test varchar2(32767) := '';
7 start_time timestamp;
8
9 begin
10
11 select count(*)
12 into k
13 from tmp_test;
14
15 for i in 1 .. k loop
16 start_time := systimestamp;
17 begin
18
19 select wm_concat(a) into v_test
20 from tmp_test
21 where rownum < i;
22
23 exception when others then
24 dbms_output.put_line('wm_concat: ' || length(v_test));
25 dbms_output.put_line(systimestamp - start_time);
26 exit;
27 end;
28 end loop;
29
30 for i in 1 .. k loop
31 start_time := systimestamp;
32
33 select stragg(a) into v_test
34 from tmp_test
35 where rownum < i;
36
37 if v_test = 'OVERFLOW' then
38 dbms_output.put_line('stragg: ' || length(v_stragg));
39 dbms_output.put_line(systimestamp - start_time);
40 exit;
41 else v_stragg := v_test;
42 end if;
43 end loop;
44 end;
45 /
wm_concat: 3976
+000000000 00:00:00.005886000
stragg: 3976
+000000000 00:00:00.005707000
PL/SQL procedure successfully completed.
至于解决，恐怕你做不到.一旦达到该极限就可以了.您将必须找到一种不同的汇总方法，或者问自己是否确实需要.
As for solving it, I'm afraid you can't. Once you hit that limit that's it. You'll have to find a different way of doing your aggregations or ask yourself if you really need to.
这篇关于如何连接字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！