dbms_output.put_line problem

on one of the application, they have dbms_output.put_line and it launch with application, every ten days their services crashed and I think what happened was dbms_output.put_line was writing to the buffer. my question is does Oracle have package to clean up the buffer per session????

Hi.

You can empty the pipe using DBMS_OUTPUT.get_line or DBMS_OUTPUT.get_lines. This is how SQL*Plus and other Oracle tools retrieve the server output.

A better approach is to wrap the output lines in your own procedure that can be switched off. That way you only put things on the pipe if you are using them. I use the following:

http://www.oracle-base.com/DBA/Miscellaneous/dsp.pks
http://www.oracle-base.com/DBA/Miscellaneous/dsp.pkb

It allows you to switch off output, output to files, output different datatypes and wrap output that is longer than 255 etc. Several people have similar code, but this is my take on it.

You should be able to replace your DBMS_OUTPUT calls with a simple search and replace then you're sorted.

Cheers

Tim,

I see you open & close the file for each line. Wouldn't using utl_file.fflush be more efficient? (Assuming heavy use.) Of course you have to handle the open and close differently.

BTW, this is an excellent technique for debugging - with a flag to turn it on/off, you can leave it in the production system for those occasions when the test data works OK, but production stuff falls flat on its face.

Hi.

I agree that the FOPEN and FCLOSE for each action is an overhead but I've found that more reliable than worrying about the state of the file between runs. In addition the close makes it possible for multiple sessions to write to the same file with fewer clashes.

The loss of speed doesn't bother me too much because it wouldn't "normally" be running in production.

It's proved very usefull for me. I'm using it as we speak to debug some dataloads I'm doing.

Cheers

Quote:

---

Originally posted by TimHall
In addition the close makes it possible for multiple sessions to write to the same file with fewer clashes.

---

I generate the file name from the AUDSID to avoid this - depends what you want to do.

Quote:

---

Originally posted by TimHall
The loss of speed doesn't bother me too much because it wouldn't "normally" be running in production.

---

Candyman doesn't say, but I got the impression that there was a lot of i/o.

This oracle-base site has some good stuff on it. Any idea who runs it? ;)