Debug the stored procedure using the dbms_debug package

 

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,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 shocould 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 PCIe. SQL Sets a breakpoint Ideally, these files shocould be stored in the directory specified with the environment variable sqlpath so that they can be executed with privileges.Steps required to debugging a PL/SQL BlockWe 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 packageSpecification: 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 debuggerIn order to debug PL/SQL with dbms_debug, two sessions are needed:DebugeeSession andDebuggerSession. 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 debugeeThe debugee is started with DBE: SQL> @ DBE session altered. START_DEBUGEE----------------------------------------------------0009000A0001 The string that is printed (0009000a0001In this case) will be used when the debugger will be started. Now, an anonymous PL/SQL is executed in the debugee session: SQL>Declare2V_result integer;3Begin4Select pkg_dbgd.tst_1 (4) into v_result from dual;5End;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 debuggerNow, an another session (called the debugger session) is attached to the debugee with DBR: SQL> @ dbrenter value for debugee_id:0009000a0001Runtime infoprg name: Line: terminated: 0 breakpoint: stackdepthinterpr 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 breakpointWith s8it is possible (and almost a must) to set a breakpoint. In the following case, the breakpoint is set on line 4: SQL> @ brlenter value for 1:4Breakpoint set: 1 Running the debugeeIt'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 VariablesCont_lines_before _AndCont_lines_after _.SteppingWe want to know whats going on inPkg_dbgd_tst_1, So weStepThis 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 ); SteppingNo, we step to the next executable line (with S:Adp_debug's package DefinitionAdp_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 (Release in varchar2 ); function start_debugee return varchar2; Procedure print_proginfo (prginfo dbms_debug.program_info );