OraFAQ Forum: SQL & PL/SQL » DBMS

Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_OUTPUT (11g)

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

DBMS_OUTPUT [message #642725]

Thu, 17 September 2015 10:39

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

Hi friends,
I am getting below error:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
even though i did some google and tried to change "set serveroutput on size 1000000"
but getting same problem..Please help me..
Thanq

Report message to a moderator

Re: DBMS_OUTPUT [message #642726 is a reply to message #642725]

Thu, 17 September 2015 10:40

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

I am getting this error when i tried to call procedure

BEGIN
XEBS_PO_REQUISITIONS_PKG.MAIN;
END;
/

Report message to a moderator

Re: DBMS_OUTPUT [message #642728 is a reply to message #642726]

Thu, 17 September 2015 10:46

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Have you tried setting serveroutput off?

Report message to a moderator

Re: DBMS_OUTPUT [message #642729 is a reply to message #642728]

Thu, 17 September 2015 10:50

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

Hi thanks for the reply,
I tried to execute set serveroutput off but encountered with :

ORA-04031: unable to allocate 97272 bytes of shared memory ("large pool","unknown object","QERHJ");

Report message to a moderator

Re: DBMS_OUTPUT [message #642730 is a reply to message #642729]

Thu, 17 September 2015 10:52

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

What exact command did you issue to get that error?
Copy and paste the session

Report message to a moderator

Re: DBMS_OUTPUT [message #642732 is a reply to message #642730]

Thu, 17 September 2015 10:54

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

Hi i just tried :

SQL> set serveroutput on size 1000000

Report message to a moderator

Re: DBMS_OUTPUT [message #642733 is a reply to message #642725]

Thu, 17 September 2015 10:55

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

SET SERVEROUTPUT ON SIZE UNLIMITED

SY.

Report message to a moderator

Re: DBMS_OUTPUT [message #642734 is a reply to message #642733]

Thu, 17 September 2015 11:08

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

Even though when i tried above statement but still getting error:
for simple select statement also..

ORA-04031: unable to allocate 1048 bytes of shared memory

Report message to a moderator

Re: DBMS_OUTPUT [message #642736 is a reply to message #642734]

Thu, 17 September 2015 11:42

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So you fill too much the dbms_output buffer for your SGA (although I thought this buffer was in UGA).
What is the COMPLETE error message.
Or better, copy and paste your SQL*Plus session.

[Updated on: Thu, 17 September 2015 11:43]

Report message to a moderator