| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: 2 bizarre PL/SQl behaviors
A copy of this was sent to hans.xie_at_its.csiro.au (Hans Xie)
(if that email address didn't require changing)
On Fri, 18 Sep 98 06:35:37 GMT, you wrote:
>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?
>
you allowed for 32,767 BYTES of data to be printed. 1, 2, 3, ....., 100, 101, ...., 10000 takes more then 32k of text to be printed...
try dbms_output.enable(1000000);
>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?
>
you can only fetch SQL datatypes from SQL queries. in v7, a varchar2 is limited to 2000 characters. You cannot return a varchar bigger then 2000 characters from pl/sql TO SQL (in general you can return >2000 characters from pl/sql, just not when called from sql)
>Thanks in advance.
>
>HB Xie
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Sep 19 1998 - 14:06:43 CDT
 |
 |