OraFAQ Forum: SQL & PL/SQL » BUFFER OVERFLOW PROBLEM

Home » SQL & PL/SQL » SQL & PL/SQL » BUFFER OVERFLOW PROBLEM

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

BUFFER OVERFLOW PROBLEM [message #263799]

Fri, 31 August 2007 01:37

sqlc
Messages: 6
Registered: August 2007

Junior Member

Hi

I am getting a buffer overflow problem.The error is

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 41

The code's attached.The scenario is i have 3 tables one with users (user),one with menus for the user(menu) and another one with permission for the menus this user has(perm).

now this perm table has a field programid it is a concatenated list of all menus(each menu has a 2 digit code),
i have to cut out each 2 digit code and find it's corresponding name in the menu table.
so the final output would be
user1

list of all programs of user1

user2
list of all programs of user2

please do help me.thanks in advance

Regards
sqlbeginner

Attachment: USER ROLES.doc
(Size: 21.00KB, Downloaded 504 times)

Report message to a moderator

Re: BUFFER OVERFLOW PROBLEM [message #263806 is a reply to message #263799]

Fri, 31 August 2007 01:50

Maaher
Messages: 7065
Registered: December 2001

Senior Member

DBMS_OUTPUT has a default buffer size of 2000 bytes. In Oracle versions prior to 10gR2 you could increase to 1000000 bytes. As of 10gR2 you can set it to "unlimited". 10gR2 also lifted the 256 byte line size limit.

so, if you're on 10gR2 or 11g you can set the serveroutput to unlimited and you're problem is solved.

MHE

Report message to a moderator

Re: BUFFER OVERFLOW PROBLEM [message #263809 is a reply to message #263799]

Fri, 31 August 2007 01:51

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Also,
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Re: BUFFER OVERFLOW PROBLEM [message #263895 is a reply to message #263799]

Fri, 31 August 2007 04:18

thisisnatesh
Messages: 92
Registered: March 2007

Member

Hi,
Please increase the buffer size
in your plsql code in the beginning by using

"dbms_output.enable(<size>);"

where size based on your version as discussed in previous replies.

Regards

Report message to a moderator

Re: BUFFER OVERFLOW PROBLEM [message #264129 is a reply to message #263799]

Sat, 01 September 2007 00:23

lenin_babu55
Messages: 12
Registered: August 2007

Junior Member

hii

plz check the for loop in the program some times this problem occurs due to the loops use 'exit' also ...............

or else set buffer size to increase size

Report message to a moderator

Previous Topic:	displaying rows into columns
Next Topic:	issue with to_date and to_char

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 20:35:39 CST 2024