dbms_debug

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 utility 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 (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;

INITIALIZE

----------------------------------------------

000900DB0001

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.

ALTER SESSION SET PLSQL_DEBUG = true;

Setting a Breakpoint

The code below, submitted from the debug session, will set a breakpoint on line 6 (ret := ret + 1;) of the function that SCOTT created in the target session.

SQL-DEBUG> set serveroutput on

SQL-DEBUG> declare

2 info dbms_debug.program_info;

3 bnumber binary_integer;

4 return_int binary_integer;

5 begin

6 info.namespace := dbms_debug.namespace_pkgspec_or_toplevel;

7 info.name := 'LOOP_TESTER';

8 info.owner := 'SCOTT';

9 info.dblink := null; -- no dblink access

10 info.line# := 6; -- line we want the break

11 return_int:= dbms_debug.set_breakpoint(info,3,bnumber);

12 -- check for returned error

13 if return_int != dbms_debug.success then

14 dbms_output.put_line('Error Setting breakpoint');

15 else

16 dbms_output.put_line('Breakpoint set!');

17 end if;

18 end;

19 /

Breakpoint set!

PL/SQL procedure successfully completed. That is a lot of work just to set a breakpoint! Most Oracle developers that utilize the dbms_debug package usually create a wrapper package of their own that makes it easier to use directly from the SQL*Plus prompt without requiring anonymous PL/SQL blocks. This package would be created once by the developer and used repeatedly instead of accessing dbms_debug directly through anonymous PL/SQL calls, as in the example above.

Synchronizing

Synchronization is the process of the debug session waiting for the target session to execute and hit a breakpoint. The following code will be executed from the debug session and will hang until a breakpoint occurs.

SQL-DEBUG> DECLARE

2 runinfo dbms_debug.runtime_info;

3 retval binary_integer;

4 BEGIN

5 retval := dbms_debug.synchronize(runinfo, 0 +

6 dbms_debug.info_getstackdepth +

7 dbms_debug.info_getbreakpoint +

8 dbms_debug.info_getlineinfo + 0);

9 if retval = dbms_debug.success then

10 dbms_output.put_line(' Sync Successful ');

11 else

12 dbms_output.put_line(' Sync Unsuccessful error = '

13 || retval);

14 end if;

15 END;

16 /

Sync Successful

The next step is to call the function and debug the code. The statement below will execute the loop_tester function and cause the breakpoint to be hit:

SQL-TARGET> select loop_tester(500) from dual;

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 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.

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.