how to concatenate strings?

Question

I'm on Oracle 10g and have the following table structure: id, paragraph

I want to group by id and concatenate the paragraphs. Each paragraph maybe 1500 characters or more.

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

how do I solve this?

Answer 1

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.

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.

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.