Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: DBMS_ output.put_line ERROR
There is no adequate solution to this in my opinion. Other programming
languages have some way to output immediately, Oracle has this kludge called
dbms_output. This waits until your script is finished before printing and it
has a max limit of 1,000,000 bytes that it will hold. As you found out it is
inadequate for anything but 'light' use.
In Reports I use a custom procedure that writes to a client side file using text_io (which is only available to forms and reports it seems).
Outside of reports/forms your choice is utl_file (as noted by others responding to your question). The problem with this is that the file is created on the server, so you need access to whatever system that is on and rights to view files in the directory where the DBA has established permission to write those files.
The other option seems to be another kludge where you write to a pipe that connects to some other process, but I am not clear on all the requirements for that.
Needless to say, at least from my standpoint, this shouldn't be.
"Mike Aloms" <alomski_at_hotmail.com> wrote in message
news:fc5ce0ff.0108211406.30207f9_at_posting.google.com...
> Hi Gurus
>
> Can some please tell me how to get round the error below
>
> ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
> ORA-06512: at line 118.
>
> I was using DBMS_OUTPUT.PUT_LINE to print out some data from the
> database when I ran into the error.
>
> In my script, I did
> dbms_output.disable; dbms_output.enable(1000000); at the beginning of
> the execution section of my pl/sql block.
>
> So do I have to look for another way of writing my data to a file or
> is it possible to flush the buffer?
>
> Many Thanks
> Mike
Received on Wed Aug 22 2001 - 07:18:02 CDT