Home » SQL & PL/SQL » SQL & PL/SQL » ERROR - Buffer overflow, limit of 1000000 bytes (Oracle 9i)
ERROR - Buffer overflow, limit of 1000000 bytes [message #358007] Fri, 07 November 2008 13:09 Go to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

Ok, dumb question. I have a shell-script that logs into Oracle SQL-Plus and prints to a file appx 200-300k lines of data. It prints out the lines from DBMS_OUTPUT. There is a limit to this, of course, and it errors with a Buffer-Overflow when it hits around 8-9k lines of output.

Is there a simple way around this? If not, what is the "standard" for doing something like this? I just have a simple query, and the results of the query need to go, line-by-line, into a comma-separated file. Thoughts?

Thanks in advance!!
Re: ERROR - Buffer overflow, limit of 1000000 bytes [message #358010 is a reply to message #358007] Fri, 07 November 2008 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has been answered a couple of hours ago.
Please search BEFORE posting.

Regards
Michel
Re: ERROR - Buffer overflow, limit of 1000000 bytes [message #358011 is a reply to message #358010] Fri, 07 November 2008 13:58 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

If you are referring to post:
http://www.orafaq.com/forum/t/127355/79373/

My issue is not the same. They have a line-length overflow. My issue is not a line-length overflow, it's a buffer overflow for the entire output (not just per line). Having a difficult time finding a work-around for it, although there are plenty of work-arounds for the 255 line-limit.

And I always search before posting, to avoid nasty, terse replies... I'm still searching as we speak, so hopefully I'll find something soon. Just thought I would throw this out there if someone had a suggestion.

Thanks

EDIT: forgot to mention that my client is NOT on the same server as the actual database, so UTL_FILE will not work for me Sad

[Updated on: Fri, 07 November 2008 14:07]

Report message to a moderator

Re: ERROR - Buffer overflow, limit of 1000000 bytes [message #358014 is a reply to message #358011] Fri, 07 November 2008 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no workaround with dbms_output but you could use DBMS_PIPE sending the message in one session and receiving it in another one or in the same one after completion like dbms_output.

Regards
Michel
Re: ERROR - Buffer overflow, limit of 1000000 bytes [message #358016 is a reply to message #358014] Fri, 07 November 2008 14:35 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

I like this idea! Working on it now.

Thanks so much for the advice!
Re: ERROR - Buffer overflow, limit of 1000000 bytes [message #358122 is a reply to message #358016] Sat, 08 November 2008 15:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
Scroll down to workaround 3 for Tom Kyte's my_dmbs_output package in the following thread. It does not have either of the limitations.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:146412348066
Re: ERROR - Buffer overflow, limit of 1000000 bytes [message #358219 is a reply to message #358122] Mon, 10 November 2008 03:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is there a reason taht you can't just SPOOL the output from part o f the SQL*Plus session to a file?
Re: ERROR - Buffer overflow, limit of 1000000 bytes [message #358292 is a reply to message #358219] Mon, 10 November 2008 10:11 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

I feel quite dumb now.. I probably should have been doing that all along. But yes, I'm now spooling to a file, and it's working as expected! Thank you!!

If interested, the pl-sql looks like the following:

set head off;
set linesize 2000;
set colsep ',';
set newpage 0;
spool file.csv;

SELECT field1 || ',' || field2 || ',' || etc...
FROM table
WHERE etc...
/

[Updated on: Mon, 10 November 2008 10:11]

Report message to a moderator

Re: ERROR - Buffer overflow, limit of 1000000 bytes [message #358328 is a reply to message #358292] Mon, 10 November 2008 13:45 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You might like to add "set trimspool on" to reduce logfile size (trims trailing spaces - else they'll be the 2000 you set linsize to).
Re: ERROR - Buffer overflow, limit of 1000000 bytes [message #358339 is a reply to message #358328] Mon, 10 November 2008 14:58 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

Thanks for the input! I added that line to my pl-sql.

Something strange.. It's adding a ^L character at the beginning of every 13th line of the spool'd output.. Not a huge deal since I can remove it with a SED command in the script, but any thoughts on why it does that?
Re: ERROR - Buffer overflow, limit of 1000000 bytes [message #358340 is a reply to message #358339] Mon, 10 November 2008 15:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set pagesize 50000

Regards
Michel
Re: ERROR - Buffer overflow, limit of 1000000 bytes [message #358341 is a reply to message #358340] Mon, 10 November 2008 15:47 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

That did the trick! Thanks Michel!!

ONE more question for you guys, and I promise to stop being a bother - At the bottom of the file it still prints "250000 rows selected." Ideally I'd like for this to not show, but once again, I can use the unix script to remove the line. However, if it's possible in the pl-sql I'd prefer that.

Thoughts? Thanks again guys.
Re: ERROR - Buffer overflow, limit of 1000000 bytes [message #358344 is a reply to message #358341] Mon, 10 November 2008 15:58 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SET FEEDBACK OFF

By the way, here are all SET System Variables available on Oracle 10g so - take a look; perhaps you'll find something interesting.
Re: ERROR - Buffer overflow, limit of 1000000 bytes [message #358345 is a reply to message #358344] Mon, 10 November 2008 16:17 Go to previous message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

As always, worked like a charm! Great link too! I'll scan it over. I learned a lot with this little simple exercise, so thank you guys VERY much for your help!
Previous Topic: copy view to table with conditions
Next Topic: Need help
Goto Forum:
  


Current Time: Fri Dec 02 19:05:01 CST 2016

Total time taken to generate the page: 0.06255 seconds