Home » SQL & PL/SQL » SQL & PL/SQL » buffer overflow exception handling
buffer overflow exception handling [message #144126] Tue, 25 October 2005 03:38 Go to next message
Mibber
Messages: 3
Registered: October 2005
Location: Amsterdam
Junior Member
Hello guys and girls,

what a great place you have here. Just what I've been looking for, since I'm trying to learn PL/SQL.
I've been working with oracle a few weeks now, and today I received the oracle error code:

ORA-20000: ORU-10027: buffer overflow

I've searched the forum but couldn't find an exception handler, it seems as if the only solution is to change the serveroutput buffer size higher. I wonder if there isn't a way to not have to change that buffer size, but to just show an error message.

I've tried it with: exception when others... but it didn't work.

Thanks in advance.

Report message to a moderator

Re: buffer overflow exception handling [message #144184 is a reply to message #144126] Tue, 25 October 2005 07:22 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
This is because output buffer of DBMS_OUTPUT has the default size
2000.
Use SET SERVEROUTPUT ON SIZE n, where n - required value (up to
1000000).

SQL> begin
  2   for i in 1..20 loop
  3    dbms_output.put_line(lpad('0',100,'1'));
  4   end loop;
  5  end;
  6  /
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
begin
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 158
ORA-06512: at "SYS.DBMS_OUTPUT", line 121
ORA-06512: at line 3

SQL> set serveroutput on size 100000
SQL> begin
  2   for i in 1..20 loop
  3    dbms_output.put_line(lpad('0',100,'1'));
  4   end loop;
  5  end;
  6  /
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110
11111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111110

PL/SQL procedure successfully completed.

Rgds.

[Updated on: Tue, 25 October 2005 07:22]

Report message to a moderator

Re: buffer overflow exception handling [message #144187 is a reply to message #144126] Tue, 25 October 2005 07:35 Go to previous messageGo to next message
Mibber
Messages: 3
Registered: October 2005
Location: Amsterdam
Junior Member
Thanks dmitry,

I know I can change the buffer size, but what I would like is to show the user a message. I'm comparing two tables, and showing the differences with the dbms_output.put_line.
So when one of the two tables differs too much, it will return too many rows which will make the output buffer too small.

Now what I want is to show a message that there are too many differences between the two tables to continue running the package.

Report message to a moderator

Re: buffer overflow exception handling [message #144189 is a reply to message #144187] Tue, 25 October 2005 07:39 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Store the differences in a table and query that table afterwards. No more buffer overflow Wink.

MHE

Report message to a moderator

Re: buffer overflow exception handling [message #144191 is a reply to message #144126] Tue, 25 October 2005 07:46 Go to previous messageGo to next message
Mibber
Messages: 3
Registered: October 2005
Location: Amsterdam
Junior Member
Thanks, that would work!
How could I've not thought about that... thanks again.

Report message to a moderator

Re: buffer overflow exception handling [message #144199 is a reply to message #144191] Tue, 25 October 2005 08:02 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I just remembered that Tom Kyte has provided an alternative here.

MHE

Report message to a moderator

Previous Topic: Bulk insert
Next Topic: update table.column from another table.column
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Aug 22 18:22:55 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.