The Oracle (tm) Users' Co-Operative FAQ
How can I get a PL/SQL procedure to output debugging messages.
| Author's name: Keith Jamieson Author's Email: keith_jamieson@hotmail.com |
Date written: 25th July 2001 Oracle version(s): 7.3.4,8.0.6,8.1.7 |
| When people first start to code and debug Oracle procedures, they start using DBMS_OUTPUT.PUT_LINE, and when they test the code using SQLPLUS, their debug messages do not appear. Sometimes debug messages appear before a procedure call, but not afterwards. |
The most important aspect is to tell sqlplus to display output from the server to the screen. This can be done by issuing the following sqlplus command: set serveroutput on. I have a couple of simple rules which get me around most problems:
I always issue set serveroutput on before running a procedure that I want to debug.
I always issue a dbms_output.enable statement after a call to a procedure. This is because a dbms_output.disable statement may have been issued by any procedure which may have been called during the code execution.
In addition, after I have made any appropriate changes, and recompiled the procedure, I again issue set serveroutput on before re-executing the code.
There is an alternative, to using dbms_output, and that is to create a table especially for debugging purposes, and insert your error messages into this table. If you want to see these messages in another session, then of course, you will have to have committed your transaction.
For serious debugging purposes, eg extremely large or complex programs, or tracing code execution, I strongly advocate the use of a PL/SQL editing/debugging tool. There are a number of such tools on the market, provided by both Oracle(tm) and other vendors.
Further reading: SQL*PLUS Users Guide and Reference. See also the FAQ item: How can I get a PL/SQL procedure to output high volumes of debugging messages ?
