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????
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.
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.