The Oracle (tm) Users' Co-Operative FAQ
How can I get a PL/SQL procedure to output high volumes of debugging messages?
| Author's
name: Raj Jamadagni Author's Email: rajendra dot jamadagni at espn dot com |
Date
written: 08/20/2001 Oracle version(s): Oracle 7.3 and later |
| Whenever I needed some kind of feedback from a plsql procedure, I used to rely on dbms_output for displaying messages. This worked fine, until I realised that the messages won’t be displayed until after the code is executed completely. So I found another solution … |
Oracle supplied package dbms_pipe is of great help, and my solution is based around this package. There are two things needed, a stored procedure and a listening program.
The stored procedure accepts a message string and optionally a pipe name. It simple puts the message on the default pipe (unless pipe name is specified explicitly).
The listening program can be anything that can read an oracle database pipe. This program is basically a loop, that sleeps for a while, wakes up, checks the pipe for any messages and repeats. If a message is received, it can be processed as per individual requirements, I usually print it to stdout. Oracle has provided a skeleton program called ‘daemon.c’ that demonstrates a pipe listener.
I prefer this technique over dbms_output, for the following reasons
This works across clients i.e. I can use this procedure to dump messages from my Oracle Form, as well as server code. So this gives me a complete picture of execution.
The messages put on the pipe can be viewed instantly (actually based on polling time specified for the listening daemon), so I can see messages as the execution progresses.
This doesn't have 1MB limits, that of dbms_output.
I embedded debugging code in my applications in such a manner that it allows me to turn on/off this tracing at runtime without any modifications to my code.
Note: There is some learning curve involved in this, but here is my experience, it took me few hours to experiement and set this up, but in last 3+ years I haven’t touched the code.
Further reading: Start with ‘$ORACLE_HOME/rdbms/admin/dbmspipe.sql’ followed by ‘Oracle Application Developers Guide’ for detailed explaination about dbms_pipe package. See also the FAQ item: How can I get a PL/SQL procedure to output debugging messages ?
