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

Home
 
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


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Utilities Tips

The dbms_debug Utility

Viewing and Modifying Variables

Once the breakpoint is hit, the debug facility pauses execution of the program. The power of any debugger is in its ability to inspect and change variable values at runtime. The code below will get the value of the “ctr” variable and display it in SQL*Plus:

SQL-DEBUG> DECLARE
2 frame number;
3 varvalue VARCHAR2(500);
4 retval binary_integer;
5 varname varchar2(32);
6 BEGIN
7 varname := 'CTR';
8 frame := 0 ;
9 retval := dbms_debug.get_value(varname, frame, varvalue, NULL);
10 dbms_output.put_line('The value of the variable ' || varname ||
11 ' is ' || varvalue);
12 END;
13 /

The value of the variable CTR is 500

PL/SQL procedure successfully completed.

So far, everything looks correct. A value of 500 was passed to the function, and the value returned by the get_value procedure was the same. Next, the set_value procedure will be executed in order to change the value to 700:

SQL-DEBUG> DECLARE
2 frame number;
3 retval binary_integer;
4 BEGIN
5 frame := 0;
6 retval := dbms_debug.set_value (frame, 'ctr := 700;');
7 if retval = dbms_debug.success then
8 dbms_output.put_line('Variable change successful');
9 else
10 dbms_output.put_line('Variable change unsuccessful');
11 end if;
12 END;
13 /

Variable change successful

The variable “ctr” is once again retrieved via get_value, and the output reveals that the new value has been loaded into the program.

SQL> DECLARE
2 frame number;
3 varvalue VARCHAR2(500);
4 retval binary_integer;
5 varname varchar2(32);
6 BEGIN
7 varname := 'CTR';
8 frame := 0 ;
9 retval := dbms_debug.get_value(varname, frame, varvalue, NULL);
10 dbms_output.put_line('The value of the variable ' || varname ||
11 ' is ' || varvalue);
12 END;
13 /

The value of the variable CTR is 700

PL/SQL procedure successfully completed.

The developer can then choose to continue execution (dbms_debug.continue), add more breakpoints, delete breakpoints, or whatever is needed to analyze the issues with the code.

The ability to inspect and modify variable values at runtime is a prerequisite for any development environment. It allows developers to test code during the actual execution without writing additional code for the sole purpose of testing.

Many procedures exist within the dbms_debug package. A developer can utilize these to create a complete debug environment, set break points, inspect variable values, pause program execution, etc. When errors occur during debugging, one way to map the error numbers to messages is to view the source for dbms_debug located in $ORACLE_HOME/rdbms/admin/dbmspb.sql.

dbms_debug is a powerful, yet somewhat difficult to use, utility for Oracle developers. It is easy to see why third party tools (Figure 8.2) are successful with products that serve as an API to dbms_debug, with a robust user interface and insulation from the details of the utility.


Figure 8.2 – SQL Programmer (Third Party Product)
Developers should leverage this utility for inspecting and changing runtime variables instead of inserting code that writes values to tables. Once mastered, dbms_debug is impossible to live without.
 


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.