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
----------------------------------------------------
0009000A0001

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: 0009000A0001

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

  function  start_debugee return varchar2;
 
  procedure print_proginfo(prginfo dbms_debug.program_info);
 
  procedure print_runtime_info(runinfo dbms_debug.runtime_info);

  procedure print_source(
    runinfo       dbms_debug.runtime_info,
    lines_before  number default 0,
    lines_after   number default 0
  );


  procedure print_runtime_info_with_source(
                 runinfo        dbms_debug.runtime_info 
                 --v_lines_before in number, 
                 --v_lines_after  in number,
                -- v_lines_width  in number
                 );
 
  procedure self_check;

  procedure set_breakpoint(p_line in number, p_name in varchar2 default null, p_owner in varchar2 default null,
    p_cursor   in boolean default false,
    p_toplevel in boolean default false,
    p_body     in boolean default false,
    p_trigger  in boolean default false);

  procedure step;
 
  procedure step_into;

  procedure step_out;

  function  str_for_namespace(nsp in binary_integer) return varchar2;

  function  str_for_reason_in_runtime_info(rsn in binary_integer) return varchar2;

  procedure wait_until_running;

  procedure is_running;

  procedure version;

  procedure detach;

  function  libunittype_as_string(lut binary_integer) return varchar2;

  function  bp_status_as_string(bps binary_integer) return varchar2;

  function  general_error(e in binary_integer) return varchar2;

  -- the following vars are used whenever continue returnes and shows
  -- the lines arount line
  cont_lines_before_ number;
  cont_lines_after_  number;
--  cont_lines_width_  number;

  -- Store the current line of execution
  cur_line_ dbms_debug.runtime_info;

end;
/

The package body:

adp_debug's body

adp_debug_body.sql

create or replace package body adp_debug as

  procedure abort is
    runinfo dbms_debug.runtime_info;
    ret     binary_integer;
  begin
    continue_(dbms_debug.abort_execution);
  end abort;


  procedure backtrace is
    pkgs dbms_debug.backtrace_table;
    i    number;
  begin
    dbms_debug.print_backtrace(pkgs);
    i := pkgs.first();
    dbms_output.put_line('backtrace');
    while i is not null loop
      dbms_output.put_line('  ' || i || ': ' || pkgs(i).name || ' (' || pkgs(i).line# ||')');
      i := pkgs.next(i);
    end loop;
   exception
    when others then
     dbms_output.put_line('  backtrace exception: ' || sqlcode);
     dbms_output.put_line('                       ' || sqlerrm(sqlcode));
  end; -- backtrace


  
  procedure breakpoints is
    brkpts dbms_debug.breakpoint_table;
    i      number;
    v_line number;
  begin
    dbms_debug.show_breakpoints(brkpts); 
    i := brkpts.first();
    dbms_output.put_line('');
    while i is not null loop
      if v_line is not null then
      dbms_output.put( to_char(v_line , '99999'));
      null;
      else
      dbms_output.put('      ');
      end if;

      dbms_output.put( ' ');

      dbms_output.put(to_char(i,'999') || ': ');
      dbms_output.put(rpad(coalesce(brkpts(i).name, ' '), 31));

      dbms_output.put(rpad(coalesce(brkpts(i).owner,' '), 31));

      v_line:=brkpts(i).line#;


      dbms_output.put( ' ');
      dbms_output.put(libunittype_as_string(brkpts(i).libunittype));
      dbms_output.put( ' ');
      dbms_output.put(bp_status_as_string  (brkpts(i).status     ));
     
      dbms_output.put_line('');
      i := brkpts.next(i);
    end loop;
  end breakpoints;

  /* 

      dbms_debug.continue can be called with the following breakflags:
       o  break_next_line       ( Break at next source line (step over calls) ) 
       o  break_any_call        ( Break at next source line (step into calls) )
       o  break_any_return    
       o  break_return        
       o  break_exception     
       o  break_handler       
       o  abort_execution     

       As the user of adp_debug might want to use continue with variying breakflags, continue_ (with the
       underscore) is the generic wrapper. (I hope this makes sense)

  */


  function libunittype_as_string(lut binary_integer) 
  return varchar2 is
  begin

    if lut = dbms_debug.libunitType_cursor         then return 'Cursor'; end if;
    if lut = dbms_debug.libunitType_procedure      then return 'Proc'  ; end if;
    if lut = dbms_debug.libunitType_function       then return 'Func'  ; end if;
    if lut = dbms_debug.libunitType_function       then return 'Func'  ; end if;
    if lut = dbms_debug.libunitType_package        then return 'Pkg'   ; end if;
    if lut = dbms_debug.libunitType_package_body   then return 'Pkg Bd'; end if;
    if lut = dbms_debug.libunitType_trigger        then return 'Trig'  ; end if;
    if lut = dbms_debug.libunitType_unknown        then return 'Unk'   ; end if;

    return '???';

  end libunittype_as_string;

  -- "User friendly" name for breakpoint_status_*
  function  bp_status_as_string(bps binary_integer) return varchar2 is
  begin
   
    if bps = dbms_debug.breakpoint_status_unused   then return 'unused'  ; end if;
    if bps = dbms_debug.breakpoint_status_active   then return 'active'  ; end if;
    if bps = dbms_debug.breakpoint_status_disabled then return 'disabled'; end if;
    if bps = dbms_debug.breakpoint_status_remote   then return 'remote'  ; end if;
    
    return '???';

  end bp_status_as_string;

  procedure continue_(break_flags in number) is
    ret     binary_integer;
    v_err   varchar2(100);
  begin
    dbms_output.put_line('');

    ret := dbms_debug.continue(
      cur_line_,
        break_flags,
       0             +
       dbms_debug.info_getlineinfo   +
       dbms_debug.info_getbreakpoint +
       dbms_debug.info_getstackdepth +
       dbms_debug.info_getoerinfo    +
       0);
  
     if ret = dbms_debug.success then
       dbms_output.put_line('  reason for break: ' ||   str_for_reason_in_runtime_info(cur_line_.reason));
       if cur_line_.reason  = dbms_debug.reason_knl_exit then
         return;
       end if;
       if cur_line_.reason  = dbms_debug.reason_exit then
         return;
       end if;
       --print_runtime_info_with_source(cur_line_,cont_lines_before_, cont_lines_after_,cont_lines_width_);
       print_source(cur_line_, cont_lines_before_, cont_lines_after_);
     elsif ret = dbms_debug.error_timeout then 
       dbms_output.put_line('  continue: error_timeout');
     elsif ret = dbms_debug.error_communication then
       dbms_output.put_line('  continue: error_communication');
     else
       v_err := general_error(ret);
       dbms_output.put_line('  continue: general error' || v_err);
     end if;
  end continue_;


  procedure detach is
  begin
    dbms_debug.detach_session;
  end detach;

  /*
      continue (calling continue_ with break_flags = 0 ) will run until
      the program hits a breakpoint
  */

  procedure continue is
  begin
    continue_(0);
  end continue;


  procedure delete_bp(breakpoint in binary_integer) is
    ret binary_integer;
  begin
    ret := dbms_debug.delete_breakpoint(breakpoint);


    if ret = dbms_debug.success then
      dbms_output.put_line('  breakpoint deleted');
    elsif ret = dbms_debug.error_no_such_breakpt then
      dbms_output.put_line('  No such breakpoint exists');
    elsif ret = dbms_debug.error_idle_breakpt then
      dbms_output.put_line('  Cannot delete an unused breakpoint');
    elsif ret = dbms_debug.error_stale_breakpt then
      dbms_output.put_line('  The program unit was redefined since the breakpoint was set');
    else
      dbms_output.put_line('  Unknown error');
    end if;
  end; -- delete_bp


  procedure print_var(name in varchar2) is
    ret   binary_integer;
    val   varchar2(4000);
    frame. number;
  begin
    frame.:= 0;
    ret := dbms_debug.get_value(
      name,
      frame,
      val,
      null);


    if ret = dbms_debug.success then
      dbms_output.put_line('  ' || name || ' = ' || val);
    elsif ret = dbms_debug.error_bogus_frame. then
      dbms_output.put_line('  print_var: frame. does not exist');
    elsif ret = dbms_debug.error_no_debug_info then
      dbms_output.put_line('  print_var: Entrypoint has no debug info');
    elsif ret = dbms_debug.error_no_such_object then
      dbms_output.put_line('  print_var: variable ' || name || ' does not exist in in frame. ' || frame);
    elsif ret = dbms_debug.error_unknown_type then
      dbms_output.put_line('  print_var: The type information in the debug information is illegible');
    elsif ret = dbms_debug.error_nullvalue then
      dbms_output.put_line('  ' || name || ' = NULL');
    elsif ret = dbms_debug.error_indexed_table then
      dbms_output.put_line('  print_var: The object is a table, but no index was provided.');
    else
      dbms_output.put_line('  print_var: unknown error');
    end if;
 
  end print_var;


  /*

     This is the first call the debugging session must make. It, in turn, calls
     dbms_debug.attach_session.

     After attaching to the session, it waits for the first event (wait_until_running), which is interpreter starting.

  */
  procedure start_debugger(debug_session_id in varchar2) is
  begin
    dbms_debug.attach_session(debug_session_id);
    --cont_lines_before_ :=   5;
    --cont_lines_after_  :=   5;
    --cont_lines_width_  := 100;

    wait_until_running;
  end start_debugger;

  /* This is the first call the debugged session must make. 

     The return value must be passed to the debugging session and used in start_debugger
  */

  function start_debugee return varchar2 as
    debug_session_id varchar2(20); 
  begin
    --select dbms_debug.initialize into debug_session_id from dual;
    debug_session_id := dbms_debug.initialize;
    dbms_debug.debug_on;
    return debug_session_id;
  end start_debugee;
  

  procedure print_proginfo(prginfo dbms_debug.program_info) as
  begin
    dbms_output.put_line('  Namespace:  ' || str_for_namespace(prginfo.namespace));
    dbms_output.put_line('  Name:       ' || prginfo.name);
    dbms_output.put_line('  owner:      ' || prginfo.owner);
    dbms_output.put_line('  dblink:     ' || prginfo.dblink);
    dbms_output.put_line('  Line#:      ' || prginfo.Line#);
    dbms_output.put_line('  lib unit:   ' || prginfo.libunittype);
    dbms_output.put_line('  entrypoint: ' || prginfo.entrypointname);
  end print_proginfo;


  procedure print_runtime_info(runinfo dbms_debug.runtime_info) as
    --rsnt varchar2(40);
  begin
    --rsnt := str_for_reason_in_runtime_info(runinfo.reason);
    dbms_output.put_line('');
    dbms_output.put_line('Runtime Info');
    dbms_output.put_line('Prg Name:      ' || runinfo.program.name);
    dbms_output.put_line('Line:          ' || runinfo.line#);
    dbms_output.put_line('Terminated:    ' || runinfo.terminated);
    dbms_output.put_line('Breakpoint:    ' || runinfo.breakpoint);
    dbms_output.put_line('Stackdepth     ' || runinfo.stackdepth);
    dbms_output.put_line('Interpr depth: ' || runinfo.interpreterdepth);
    --dbms_output.put_line('Reason         ' || rsnt);
    dbms_output.put_line('Reason:        ' || str_for_reason_in_runtime_info(runinfo.reason));
    
    print_proginfo(runinfo.program);
  end print_runtime_info;

  procedure print_source (
    runinfo       dbms_debug.runtime_info,
    lines_before  number default 0,
    lines_after   number default 0
  ) is
    first_line binary_integer;
    last_line  binary_integer;

    prefix varchar2(  99);
    suffix varchar2(4000);

    --source_lines                 vc2_table;
    source_lines dbms_debug.vc2_table;

    cur_line         binary_integer;
    cur_real_line    number;
  begin

    first_line := greatest(runinfo.line# - cont_lines_before_,1);
    last_line  :=          runinfo.line# + cont_lines_after_    ;

    if first_line is null or last_line is null then
      dbms_output.put_line('first_line or last_line is null');
      print_runtime_info(runinfo);
      return;
    end if;

    if runinfo.program.name is not null and runinfo.program.owner is not null then

      dbms_output.put_line('');
      dbms_output.put_line('  ' || runinfo.program.owner || '.' || runinfo.program.name);

        --select 
        --   cast(multiset(
          for r in (
                select
                  -- 90 is the length in dbms_debug.vc2_table....
                  rownum line,
                  substr(text,1,90) text
                from 
                  all_source 
                where 
                  name   = runinfo.program.name   and
                  owner  = runinfo.program.owner  and
                  type  <> 'PACKAGE'              and
                  line  >= first_line             and 
                  line  <= last_line
                order by 
                  line )-- as vc2_table)
              loop 
--        into
--          source_lines
--        from 
--          dual;
        source_lines(r.line) := r.text;      

      end loop;

    else
     
      dbms_debug.show_source(first_line, last_line, source_lines);

--      select
--        cast(
--          multiset(
--            select culumn_value from 
--              table(
--                cast(source_lines_dbms as dbms_debug.vc2_table)
--              )
--        )as vc2_table)
--      into
--        source_lines
--      from 
--        dual;
    
    end if;

    dbms_output.put_line('');
    cur_line := source_lines.first();
    while cur_line is not null loop
      cur_real_line := cur_line + first_line -1;
    
   -- for r in (select column_value text from table(source_lines)) loop
        prefix := to_char(cur_real_line,'9999');

        if cur_real_line = runinfo.line# then 
          prefix := prefix || ' -> ';
        else
          prefix := prefix || '    ';
        end if;
  
  
        -- TODO, most probably superfluos, 90 is the max width.... (ts, ts)
        --if length(r.text) > v_lines_width then
        --  suffix := substr(r.text,1,v_lines_width);
        --else
        --  suffix := r.text;
        --end if;
  
        suffix := source_lines(cur_line);
        suffix := translate(suffix,chr(10),' ');
        suffix := translate(suffix,chr(13),' ');
        
        --dbms_output.put_line(prefix || suffix);
        dbms_output.put_line(prefix || suffix);
  
    --    line_printed := 'Y';
      
      cur_line := source_lines.next(cur_line);
      --cur_line := cur_line + 1;
    end loop;

    dbms_output.put_line('');

  end print_source;


  procedure print_runtime_info_with_source(
    runinfo dbms_debug.runtime_info 
    ) is


  begin

    print_runtime_info(runinfo);

    --dbms_output.put_line('line#: ' || runinfo.line#);
    --dbms_output.put_line(' -   : ' || (runinfo.line# - cont_lines_before_));

      --dbms_output.put_line('first_line: ' || first_line);
      --dbms_output.put_line('last_line:  ' || last_line);

    print_source(runinfo);

  end print_runtime_info_with_source;


  procedure self_check as
    ret binary_integer;
  begin
    dbms_debug.self_check(5);
  exception
    when dbms_debug.pipe_creation_failure     then
      dbms_output.put_line('  self_check: pipe_creation_failure');
    when dbms_debug.pipe_send_failure      then
      dbms_output.put_line('  self_check: pipe_send_failure');
    when dbms_debug.pipe_receive_failure   then
      dbms_output.put_line('  self_check: pipe_receive_failure');
    when dbms_debug.pipe_datatype_mismatch then
      dbms_output.put_line('  self_check: pipe_datatype_mismatch');
    when dbms_debug.pipe_data_error        then
      dbms_output.put_line('  self_check: pipe_data_error');
    when others then
      dbms_output.put_line('  self_check: unknown error');
  end self_check;



  /* 
     
     Out of the four parameters
       p_cursor, p_toplevel, p_body, p_trigger,
     at most one should be set to zero. They set the
     proginfo.namespace

  */
  procedure set_breakpoint(
    p_line     in number, p_name in varchar2 default null, p_owner in varchar2 default null,
    p_cursor   in boolean default false,
    p_toplevel in boolean default false,
    p_body     in boolean default false,
    p_trigger  in boolean default false) 
  as
    proginfo dbms_debug.program_info;
    ret      binary_integer;
    bp       binary_integer;
  begin

    if p_cursor then
      proginfo.namespace      := dbms_debug.namespace_cursor;
    elsif p_toplevel then
      proginfo.namespace      := dbms_debug.namespace_pkgspec_or_toplevel;
    elsif p_body then
      proginfo.namespace      := dbms_debug.namespace_pkg_body;
    elsif p_trigger then
      proginfo.namespace      := dbms_debug.namespace_trigger;
    else
      proginfo.namespace      := null;
    end if;

    proginfo.name           := p_name;
    proginfo.owner          := p_owner;
    proginfo.dblink         := null;
    proginfo.entrypointname := null;
  
    ret := dbms_debug.set_breakpoint(
      proginfo,
      p_line,
      bp);
  
    if ret = dbms_debug.success then 
      dbms_output.put_line('  breakpoint set: ' || bp);
    elsif ret = dbms_debug.error_illegal_line then
      dbms_output.put_line('  set_breakpoint: error_illegal_line');
    elsif ret = dbms_debug.error_bad_handle then
      dbms_output.put_line('  set_breakpoint: error_bad_handle');
    else
      dbms_output.put_line('  set_breakpoint: unknown error (' || ret || ')');
    end if;
  
  end set_breakpoint;

  procedure step is
  begin
    continue_(dbms_debug.break_next_line);
  end step;
 
  procedure step_into is
  begin
    continue_(dbms_debug.break_any_call);
  end step_into;

  procedure step_out is
  begin
    continue_(dbms_debug.break_any_return);
  end step_out; 

  
  function str_for_namespace(nsp in binary_integer) return varchar2 is
    nsps   varchar2(40);
  begin
    if nsp = dbms_debug.Namespace_cursor then
      nsps := 'Cursor (anonymous block)';
    elsif nsp = dbms_debug.Namespace_pkgspec_or_toplevel then
      nsps := 'package, proc, func or obj type';
    elsif nsp = dbms_debug.Namespace_pkg_body then
      nsps := 'package body or type body';
    elsif nsp = dbms_debug.Namespace_trigger then
      nsps := 'Triggers';
    else
      nsps := 'Unknown namespace';
    end if;


    return nsps;
  end str_for_namespace;
  

  function  str_for_reason_in_runtime_info(rsn in binary_integer) return varchar2 is
    rsnt varchar2(40);
  begin
    if rsn = dbms_debug.reason_none then
      rsnt := 'none';
    elsif rsn = dbms_debug.reason_interpreter_starting then
      rsnt := 'Interpreter is starting.';
    elsif rsn = dbms_debug.reason_breakpoint then
      rsnt := 'Hit a breakpoint';
    elsif rsn = dbms_debug.reason_enter then
      rsnt := 'Procedure entry';
    elsif rsn = dbms_debug.reason_return then
      rsnt := 'Procedure is about to return';
    elsif rsn = dbms_debug.reason_finish then
      rsnt := 'Procedure is finished';
    elsif rsn = dbms_debug.reason_line then
      rsnt := 'Reached a new line';
    elsif rsn = dbms_debug.reason_interrupt then
      rsnt := 'An interrupt occurred';
    elsif rsn = dbms_debug.reason_exception then
      rsnt := 'An exception was raised';
    elsif rsn = dbms_debug.reason_exit then
      rsnt := 'Interpreter is exiting (old form)';
    elsif rsn = dbms_debug.reason_knl_exit then
      rsnt := 'Kernel is exiting';
    elsif rsn = dbms_debug.reason_handler then
      rsnt := 'Start exception-handler';
    elsif rsn = dbms_debug.reason_timeout then
      rsnt := 'A timeout occurred';
    elsif rsn = dbms_debug.reason_instantiate then
      rsnt := 'Instantiation block';
    elsif rsn = dbms_debug.reason_abort then
      rsnt := 'Interpreter is aborting';
    else
      rsnt := 'Unknown reason';
    end if;
    return rsnt;
  end str_for_reason_in_runtime_info;



  procedure wait_until_running as
    runinfo dbms_debug.runtime_info;
    ret     binary_integer;
    v_err   varchar2(100);
  begin
    ret:=dbms_debug.synchronize( runinfo, 0 /*+
      dbms_debug.info_getstackdepth +
      dbms_debug.info_getbreakpoint +
      dbms_debug.info_getlineinfo   +
      dbms_debug.info_getoerinfo    +
      0 */
    );
  
    if ret = dbms_debug.success then 
      print_runtime_info(runinfo);
    elsif ret = dbms_debug.error_timeout then
      dbms_output.put_line('  synchronize: error_timeout');
    elsif ret = dbms_debug.error_communication then
      dbms_output.put_line('  synchronize: error_communication');
    else
       v_err := general_error(ret);
       dbms_output.put_line('  synchronize: general error' || v_err);
      --dbms_output.put_line('  synchronize: unknown error');
    end if;
  
  end wait_until_running;



  procedure is_running is
  begin
    if dbms_debug.target_program_running then
      dbms_output.put_line('  target (debugee) is running');
    else
      dbms_output.put_line('  target (debugee) is not running');
    end if;
  end is_running;

  function  general_error(e in binary_integer) return varchar2 is
  begin

    if e = dbms_debug.error_unimplemented then return 'unimplemented'       ; end if;
    if e = dbms_debug.error_deferred      then return 'deferred'            ; end if;
    if e = dbms_debug.error_exception     then return 'probe exception'     ; end if;
    if e = dbms_debug.error_communication then return 'communication error' ; end if;
    if e = dbms_debug.error_unimplemented then return 'unimplemented'       ; end if;
    if e = dbms_debug.error_timeout       then return 'timeout'             ; end if;

    return '???';

  end general_error;

  procedure version as
    major binary_integer;
    minor binary_integer;
  begin
    dbms_debug.probe_version(major,minor);
    dbms_output.put_line('  probe version is: ' || major || '.' || minor);
  end version;

  procedure current_prg is
    ri dbms_debug.runtime_info;
    pi dbms_debug.program_info;
    ret binary_integer;
  begin

    ret := dbms_debug.get_runtime_info(
       0             +
       dbms_debug.info_getlineinfo   +
       dbms_debug.info_getbreakpoint +
       dbms_debug.info_getstackdepth +
       dbms_debug.info_getoerinfo    +
       0,
      ri
       );

     pi := ri.program;

     print_proginfo(pi);
  end current_prg;

  begin

    cont_lines_before_ :=   5;
    cont_lines_after_  :=   5;

end;
/

dbr.sql

Starts the debugger, asks for a string that identifies the debugee. This string is obtained through running dbe.

exec adp_debug.start_debugger('&debugee_id')

dbe.sql

Starts the debugee and prints the string that identifies the debugee. This string must be used for dbr

alter session set plsql_debug=true;
select adp_debug.start_debugee from dual;

cont.sql

exec adp_debug.continue

abort.sql

exec adp_debug.abort

s.sql

exec adp_debug.step

breakpoints.sql

exec adp_debug.breakpoints

delbp.sql

exec adp_debug.delete_bp(&breakpoint_number)

is_r.sql

exec adp_debug.is_running;

si.sql

exec adp_debug.step_into

so.sql

exec adp_debug.step_out

var.sql

exec adp_debug.print_var('&1')

brl.sql

-- This script. sets a breakpoint on a line
--   Most probably to be used for anonymous blocks
exec adp_debug.set_breakpoint(p_line=>&1)

ITPub博客

Debugging PL/SQL with DBMS_DEBUG