2 bizarre PL/SQl behaviors

From: Hans Xie <hans.xie_at_its.csiro.au>
Date: Fri, 18 Sep 98 06:35:37 GMT
Message-ID: <6tsuvp$v8_001@its.csiro.au>

Hi all,

The first one, I am just curious about it:   declare
  begin
    dbms_output.enable(2000);
    for i in 1..32767 loop
      dbms_output.put_line(i);
    end loop;
  end;
It stops when i=1776 with error message:

 ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
 ORA-06512: at "SYS.DBMS_OUTPUT", line 106
 ORA-06512: at "SYS.DBMS_OUTPUT", line 73
 ORA-06512: at line 5

When I changed dbms_output.enable from 2000 to 32767. It stops when i=4296 with the same error message. Could anyone explan why?

The second one, I am very serious about it:   create or replace function ls(id_in table1.id%type)     return varchar2 is
    longstring VARCHAR2(32767);
  begin
    for rec in (select field1 from table1 where id = id_in order by field2) loop

      longstring := longstring || rec.field1;     end loop;
    return longstring;
  end;
When I use this function as 'select ls(id) from table2', it stops after reach about 14000 records with error message 'ORA 06502 PL/SQL:numberic or value error'. I tried using CONCAT instead of ||, using RTRIM for both field1 and longstring; avoiding longstring appears on both sides, etc. with no luck. It looks it fails when longstring is bigger than 2000 chars, (I guess?). I am sure longstring is far less than 32767. Could anyone shed light on?

Thanks in advance.

HB Xie Received on Fri Sep 18 1998 - 01:35:37 CDT