使用dbms_debug包调试存储过程

Debugging PL/SQL with DBMS_DEBUG

It is possible to debug PL/SQL Code in Oracle with DBMS_DEBUG. This article tries to show how to do that. Firstly, a PL/SQL Package (called ADP_DEBUG) is created that acts as a wrapper around DBMS_DEBUG. Here's the package definition and its body. Additionally, there are a few SQL statements that can be called which should make it even easier to access adp_debug: These are: dbr.sql This starts the debugger dbe.sql This starts the debuggee cont.sql Continues after a breakpoint was hit abort.sql Aborts a debugging session s.sql Steps to the next executable line breakpoints.sql Shows the breakpoints delbp.sql Deleteas a breakpoint is_r.sql Is debugee running? si.sql Steps into a function so.sql Steps out of a function var.sql Prints a variable's value brl.sql Sets a breakpoint Ideally, these files should be stored in the directory specified with the environment variable SQLPATH so that they can be executed with ease. Steps required to debugging a PL/SQL block We want to debug the following pl/sql package: create or replace package pkg_dbgd as function tst_1(i in integer) return integer; function tst_2(i in integer) return integer; end pkg_dbgd; / The package specification: create or replace package body pkg_dbgd as function tst_1(i in integer) return integer is begin if i between 5 and 10 then return 2*i; end if; if i between 0 and 4 then return tst_2(3+i); end if; if i between 6 and 10 then return tst_2(i-2); end if; return i; end tst_1; function tst_2(i in integer) return integer is begin if i between 6 and 8 then return tst_1(i-1); end if; if i between 1 and 5 then return i*2; end if; return i-1; end tst_2; end pkg_dbgd; / The debugee and the debugger In order to debug PL/SQL with dbms_debug, two sessions are needed: a debugee session and a debugger session. The debugee session is the session in which the pl/sql code to be debugged is run while the debugger session controls the debugee, that is, the debugger sets breakpoints, clears them, continues the programm and queries variable's values. Starting the debugee The debugee is started with dbe: SQL> @dbe Session altered. START_DEBUGEE ---------------------------------------------------- 0009000A 0001 The string that is printed (0009000A0001 in this case) will be used when the debugger will be started. Now, an anonymous PL/SQl is executed in the debugee session: SQL> declare 2 v_result integer; 3 begin 4 select pkg_dbgd.tst_1(4) into v_result from dual; 5 end; 6 / After typing this in, the session seems to hang. That is because the debugee sessions waits for commands from the debugger session. Starting the debugger Now, an another session (called the debugger session) is attached to the debugee with dbr: SQL> @dbr Enter value for debugee_id: 0009000A 0001 Runtime Info Prg Name: Line: Terminated: 0 Breakpoint: Stackdepth Interpr depth: 1 Reason: Interpreter is starting. Namespace:Unknown namespace Name: owner: dblink: Line#: lib unit: entrypoint: PL/SQL procedure successfully completed. The debugee won't be touched until the end, all we do now is done in the debugger session. Setting a breakpoint With brl it is possible (and almost a must) to set a breakpoint. In the following case, the breakpoint is set on line 4: SQL> @brl Enter value for 1: 4 breakpoint set: 1 Running the debugee It's time now to have the debugee run until it hits a breakpoint. cont will take care of that. SQL> @cont reason for break: Hit a breakpoint 1 declare 2 v_result integer; 3 begin 4 -> select pkg_dbgd.tst_1(4) into v_result from dual; 5 end; 6 7 8 9 As soon as a (or in our case the) breakpoint is hit, cont returns and we can enter new commands. cont also displays the line (+/- some preceeding and following lines) to make it easier to navigate in the code. The number of preceeding and following lines that are displayed are set with the package variables cont_lines_before_ and cont_lines_after_. Stepping into We want to know whats going on in pkg_dbgd_tst_1, so we step into this function using si: SQL> @si reason for break: Procedure entry RENE.PKG_DBGD 1 package body pkg_dbgd as 2 function tst_1(i in integer) return integer is 3 begin 4 -> if i between 5 and 10 then 5 return 2*i; 6 end if; 7 8 if i between 0 and 4 then 9 return tst_2(3+i); Stepping No, we step to the next executable line (with s: adp_debug's package definition adp_debug.sql --------------------- -- 90 is the length in dbms_debug.vc2_table.... --create or replace type vc2_table as table of varchar2(90); --/ create or replace package adp_debug as procedure abort; procedure backtrace; -- highly expermiental procedure current_prg; procedure breakpoints; procedure continue_(break_flags in number); procedure continue; procedure delete_bp(breakpoint in binary_integer); procedure print_var(name in varchar2); procedure start_debugger(debug_session_id in varchar2); functionstart_debugee return varchar2; procedure print_proginfo(prginfo dbms_debug.program_info); procedure print_runtime_info(runinfo dbms_debug.runtime_info);