Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: buffer overflow, limit of 2000 bytes (how to get around)
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