Question

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

我想用粗略和压缩段落。每一款的特性可能为1500个或更多。

当我尝试轮椅时,它抱怨说,铺设的缓冲太小。我实际上在Oracle网站上尝试了许多例子,他们都错过了缓冲地带太小。

select id, wm_concat(paragraph) from paragraphs group by id

我如何解决这一问题?

Answer 1

因此,对错误的猜测是:ORA-06502,我可以看到你可能认为这种情况适用于你。

但是,这是<代码>wm_concat的过失。这是一项职能,受到PLSQL最高长度为32 767人、标准Q4 000人的限制。不幸的是,我假定,由于工作方式或由于职能范围内的任何限制较低,或者由于你在选择中重新使用,你可能在上限附近任何地方使用。

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.

至于解决这个问题,我担心你会失败。一旦你打上了这一极限,这就会达到。如果您<>真的需要的话,你必须找到另一种方式来进行您的总结或询问。

友情链接