Home » SQL & PL/SQL » SQL & PL/SQL » BUFFER OVERFLOW PROBLEM
BUFFER OVERFLOW PROBLEM [message #263799] Fri, 31 August 2007 01:37 Go to next message
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
Re: BUFFER OVERFLOW PROBLEM [message #263806 is a reply to message #263799] Fri, 31 August 2007 01:50 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: BUFFER OVERFLOW PROBLEM [message #263809 is a reply to message #263799] Fri, 31 August 2007 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: BUFFER OVERFLOW PROBLEM [message #263895 is a reply to message #263799] Fri, 31 August 2007 04:18 Go to previous messageGo to next message
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
Re: BUFFER OVERFLOW PROBLEM [message #264129 is a reply to message #263799] Sat, 01 September 2007 00:23 Go to previous message
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
Previous Topic: displaying rows into columns
Next Topic: issue with to_date and to_char
Goto Forum:
  


Current Time: Fri Dec 09 07:29:24 CST 2016

Total time taken to generate the page: 0.14860 seconds