Oracle: Logging stored procedure messages

For simple cases, DBMS_OUTPUT.PUT_LINE can be used to log stored procedure messages. However, we all know that it's not useful when stored procedures are called from applications since there is no way to get the server output. Furthermore, not "draining" the server output will cause the connection to generate errors due to the output buffer being full.

One option is to use UTL_FILE such that all logging messages can be written to a file. Since UTL_FILE writes in a buffered mode, when two or more stored procedures are trying to write to the same file, we can have the output scrambled. To avoid such, FFLUSH can be invoked for each PUT_LINE. One drawback of using UTL_FILE is that the logging cannot be intermingled with the application that invokes the stored procedures. Since the application framework includes LOG4J, it perhaps is possible to create Java stored procedures that facilitate logging via LOG4J.