Oracle dbms_debug Utility breakpoint
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

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

2 runinfo dbms_debug.runtime_info;
3 retval binary_integer;
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;

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.