Oracle dbms_debug
Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Utilities Tips

The dbms_debug Utility

Oracle ships with the dbms_debug package, which is another useful programming utility for PL/SQL Developers. These programmers, when working in other languages, such as C, C++, and Java utilize an Integrated Development Environment (IDE) to peek into the runtime execution of the code by offering the following features:

• Setting breakpoints
• Inspecting variable contents
• Pause and terminate execution

Complete IDE functionality is outside the scope of this book since there are many PL/SQL IDEs on the market. The important thing to know is that they all use the dbms_debug package provided by Oracle, which is what this section explains. Without it, developers usually end up modifying the code to write values to temporary tables or to the screen during execution. This involves creating many lines of debugging code instead of using the debug facility.

The debug facility requires two sessions: the first session is used for execution of the code being debugged. Oracle refers to this session as the “target” session. The second session is the one that submits the debug commands, and for that reason, the second session is termed the “debug” session.

From the target session, a function will be created and compiled for debug.
Initializing a Debug Session
In the target session, the following function, listed below, will be created. This function does nothing more than loop for the amount specified in the function call. This function is only used as an example of something that can be debugged. In the examples below, the SQL prompt is changed to reflect the session to which it applies: SQL-TARGET or SQL-DEBUG.

SQL-TARGET> create or replace function loop_tester(ctr number) return number
2 is
3 ret number:=0;
4 begin
5 for i in 1..ctr loop
6 ret := ret + 1;
7 end loop;
8 return ret;
9 end;
10 /

Function created.

SQL-TARGET> alter function loop_tester compile debug;

Function altered.

SQL-TARGET> select dbms_debug.initialize from dual;


SQL-TARGET> exec dbms_debug.debug_on;

The debug session now will use the initialize procedure and return a number to initiate the debug session. Once the number is returned from initialize, the attach_session procedure will use it to attach from the debug session.

SQL-DEBUG> exec dbms_debug.attach_session('000900DB0001');

PL/SQL procedure successfully completed.

This function was compiled for debug explicitly. Another option is to establish debug mode for the entire session. This instructs the compiler to generate debug for all PL/SQL in the session and does not require any recomilation.


To learn more about these techniques, see the book "Advanced Oracle Utilities: The Definitive Reference". 

You can buy it directly from the publisher and get instant access to the code depot of utilities scripts.




Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.