Home » SQL & PL/SQL » SQL & PL/SQL » ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413750] Fri, 17 July 2009 08:54 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
How to avoid this below error when I am fpassing the value to a procedure through cursor?

ORU-10027: buffer overflow, limit of 1000000 bytes


Thanks
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413752 is a reply to message #413750] Fri, 17 July 2009 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is most likely an error that comes from dbms_output and you can't workaround if your version is less than 10g.

Regards
Michel
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413753 is a reply to message #413752] Fri, 17 July 2009 09:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And to be fair, anyone using Dbms_Output to pass data between procedures deserves everything they get.
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413754 is a reply to message #413753] Fri, 17 July 2009 09:15 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
In my mainprocedure,I have stored the dbms_output into a table to avoid this error. But its the other sub procedures which is using dbms_output and I dont have enough privilege to modify those.

I also tried setting serveroutput size to 1000000.

Any other way,Sir?
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413755 is a reply to message #413752] Fri, 17 July 2009 09:16 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Michel Sir,

Is there a way to overcome this in 10g?

Thanks
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413758 is a reply to message #413755] Fri, 17 July 2009 09:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to give us slightly more detail about what you're doing.

It sounds like you're using DBMS_OUTPUT as a global FIFO data transfer stack to pass data around.

Is this actually the case?
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413759 is a reply to message #413755] Fri, 17 July 2009 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
prachij593 wrote on Fri, 17 July 2009 16:16
Michel Sir,

Is there a way to overcome this in 10g?

Thanks

Yes set the buffer size to unlimited but you will have many other problems.
As others said, you likely using dbms_output in the wrong way.

Why don't you post/explain what you do or intend to do as well as your Oracle version?

Regards
Michel

[Updated on: Fri, 17 July 2009 10:02]

Report message to a moderator

Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413797 is a reply to message #413759] Fri, 17 July 2009 12:10 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Michel Sir,
As I said, in my main procedure I have an alternative to avoid dbms_output.put_line. I created a table and recorded the data.
But its the other procedure which are using DBMS_OUTPUT.PUT_LINE
to track the data flow. I can't modify those.

In my main procedure I am just loopong through cursor and pass the value to a procedure. [Sorry for unable to provide the code]


Quote:

Yes set the buffer size to unlimited but you will have many other problems.





What are the problems if we can set size to more than 1000000
say 3000000

SQL> SET SERVEROUTPUT ON SIZE 1000000000
SP2-0547: size option 1000000000 out of range (2000 through 1000000)
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 

Many thanks...

[Updated on: Fri, 17 July 2009 12:12]

Report message to a moderator

Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413798 is a reply to message #413750] Fri, 17 July 2009 12:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SET SERVEROUTPUT ON SIZE unlimited
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE	10.2.0.4.0	Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413799 is a reply to message #413797] Fri, 17 July 2009 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What are the problems if we can set size to more than 1000000 say 3000000

I didn't say 3000000 I said UNLIMITED.
And you didn't answer our questions but it does not matter it is your choice... and your problem.

Regards
Michel
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413805 is a reply to message #413799] Fri, 17 July 2009 12:39 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, which question? And whats the problem if I set to unlimited
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413806 is a reply to message #413805] Fri, 17 July 2009 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sir, which question?

Quote:
It sounds like you're using DBMS_OUTPUT as a global FIFO data transfer stack to pass data around.

Is this actually the case?

Quote:
Why don't you post/explain what you do or intend to do


In short why do you dbms_output?

Regards
Michel
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413808 is a reply to message #413806] Fri, 17 July 2009 13:12 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thats what I replied.


The dbms_output is not in the main procedure but in other calling procedures (which were used to keep track of data flow)
And now I am running that procedure as a batch. Passing values
through a cursor. Its more than 50k rows
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413810 is a reply to message #413808] Fri, 17 July 2009 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In short why do you dbms_output?

Regards
Michel
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413836 is a reply to message #413808] Fri, 17 July 2009 15:17 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
prachij593 wrote on Fri, 17 July 2009 14:12

The dbms_output is not in the main procedure but in other calling procedures (which were used to keep track of data flow)
And now I am running that procedure as a batch. Passing values
through a cursor. Its more than 50k rows


1. What "other" procedures?
2. What does "keeping track of data flow" mean?
3. What is "that" procedure?


Why don't you show us a clear structured example of what you are doing.
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413841 is a reply to message #413750] Fri, 17 July 2009 15:40 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
If you do not need the flow comments from the second procedure then

set serveroutput off

and you will have no problems.
Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes [message #413881 is a reply to message #413841] Sat, 18 July 2009 00:51 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Bill B wrote on Fri, 17 July 2009 22:40
If you do not need the flow comments from the second procedure then

set serveroutput off

and you will have no problems.

Not really, the procedure will still fill the buffer and get out of space.

Regards
Michel

Previous Topic: Devided by zero in query
Next Topic: retrieveing two values from table 1 and displaying it one record after joining it with table 2 (merg
Goto Forum:
  


Current Time: Sun Dec 11 04:19:25 CST 2016

Total time taken to generate the page: 0.05845 seconds