Question: I'm having trouble manually debugging my PL/SQL code
with display statements (utl_file.put_line). I really need a
debugger for my PL/SQL code. Does Oracle make a debugger, or do I need to
buy a third-party debug product?
Answer: I agree, PL/SQL can be problematic to debug manually.
Components of PL/SQL such as the DBMS_OUTPUT and UTL_FILE packages provided
limited debugging capabilities. More complex instrumenting of PL/SQL can also be
accomplished using DBMS_DEBUG but online debugging on PL/SQL is a different
matter.
You can use
Oracle's
PL/SQL debugger (part of the free SQL Developer suite) to step through the
PL/SQL code, one line at a time, and find any error:
PL/SQL is just like any other procedural language and you can insert display statements (using dbms_output.put_line) and step through the code and see exactly how the values of variables are changing. This is the easiest form of PL/SQL debugging.
There are common PL/SQL debugging techniques. For a full list of PL/SQL debugging tips and tricks, see Dr. Hall's book "Oracle PL/SQL Tuning Secrets", an amazing collection of PL/SQL tips for the professional programmer:
- Use display statements - You can insert dbms_output.put_line and utl_file.put_line statements to display variable states. But advanced PL/SQL can be problematic. The display components of PL/SQL (dbms_output and utl_file) provide limited PL/SQL debugging capabilities.
- Use dbms_debug - More complex instrumenting of PL/SQL can also be accomplished using the dbms_debug online debugging.
- Use dbms_profiler - The dbms_profiler package can aid in PL/SQL debugging.
- Conditional Compilation - In Oracle10g, PL/SQL conditional compilation is ideal for debugging PL/SQL.
- PL/SQL debugger - You can use Oracle's PL/SQL debugger (part of the free SQL Developer suite) to step through the PL/SQL code, one line at a time, and find any error:
and include the URL for the page.
