Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: buffer overflow, limit of 2000 bytes (how to get around)

Re: buffer overflow, limit of 2000 bytes (how to get around)

From: Scott Mattes <Scott_at_TheMattesFamily.ws>
Date: Thu, 21 Nov 2002 13:00:26 GMT
Message-ID: <KN4D9.49874$6g.8096251@news1.news.adelphia.net>


To what I am use to, DBMS_OUTPUT does not seem all that useful for debug -- any long running PL/SQL will over run the buffer and you won't see what you need AND you can't see the output until the script is all done.

I have developed a package that uses the PRAGMA AUTONOMOUS_TRANSACTION' compiler directive, found in at least 8i and higher, to save data to a table. You can select from this table while the script is running to see what is going on.

Anyone interested should let me know and I can post it.

Sybrand, I don't have a problem with the RDBMS itself, just the ugly tools Oracle slapped together to interface to it. When I can, I do my work in Delphi, but since the customer has settled on Oracle tools that is what I have to program with. I am not one of those who 'likes' to job hop, and this is a sweet telecommute job otherwise, but I don't like the tools and I think Larry should be ashamed to be making money with them.

"Tim Cross" <tcross_at_pobox.une.edu.au> wrote in message news:87n0o33mcq.fsf_at_blind-bat.une.edu.au...
> silversw2000_at_yahoo.com (Fred Zimmerman) writes:
>
> > In PL-SQL I run the following SQL Script, and
> > get the buffer overflow message; how do I get rid
> > of the message, and/or increase the buffer?
> >
>
> DBMS_OUTPUT has a buffer limit of 1000000 bytes. There is no way
> around this AFAIK.
>
> The DBMS_OUTPUT is really only useful as a debugging tool.
>
> Some workarounds are -
>
> 1. Use UTL_FILE and write the output to a file
>
> 2. Insert the data into a table and then query from that table
>
> Personally, I prefer to use the first method. I like to avoid the
> second method as it is a type of solution which tends to lead to
> temporary tables popping up all over the place. However, if you do
> decide to use method 2 and your running 8i or later, look into
> temporary global tables (or is it global temporary tables) -
> whichever, if you have/want to use temporary tables, they are better
> than just creating a normal table in your default tablespace etc.
>
> Tim
Received on Thu Nov 21 2002 - 07:00:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US