examples of debugging plsql with dbms_debug 使用DBMS_DEBUG调试PL/SQL程序

examples of debugging plsql with dbms_debug
使用DBMS_DEBUG调试PL/SQL程序

8i起, Oracle提供了DBMS_DEBUG程序包(8i之前也有), 是一种在服务器端(server-side)的调试器
它使用了一种'拉'的事件模型(a 'pull' event model), 为了调试必须建立两个数据库会话,
一个是目标会话(target session),运行要被调试的代码
另一个是调试会话(debug session), 管理目标会话


举例: 见DBMS_DEBUG: Simple example

1.开启两个会话, 会话A作为目标会话, 会话B作为调试会话


2. 会话A创建要调试的存储过程

conn a/a
drop table tt;
create table tt (a int, b int);
insert into tt values(1,1);
insert into tt values(1,2);
insert into tt values(2,3);
commit;
create or replace procedure testproc is
begin
  update tt
     set b = b*3
   where a = 1;
  dbms_output.put_line('Rows: '|| sql%rowcount);
end;
/
alter procedure testproc compile debug;

存储过程编译时要加上调试信息,用:

alter session set plsql_debug = true;

再编译
或

alter [procedure | function | package | trigger | type]  compile debug;
alter [package | type]  compile debug body;

3. 会话A初始化, 打开调试

------------
-- Note if serveroutput is set on in
-- this session Session2 will need to 
-- do an additional synchronize/continue
-- for testproc to return since sqlplus
-- calls dbms_output.get_lines which
-- will also wait to be debugged.
select dbms_debug.initialize from dual;
exec dbms_debug.debug_on

SQL> select dbms_debug.initialize from dual;

INITIALIZE
--------------------------------------------------------------------------------
0661078B0001

SQL> exec dbms_debug.debug_on

PL/SQL procedure successfully completed.

SQL>

注意,这个会话之前不能打开serveroutput, 否则在运行dbms_debug.debug_on这里会挂起

如果报错ORA-01031: insufficient privileges

SQL> exec :x := dbms_debug.initialize()
BEGIN :x := dbms_debug.initialize(); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.PBSDE", line 78
ORA-06512: at "SYS.DBMS_DEBUG", line 224
ORA-06512: at line 1


SQL>

说明缺少"DEBUG CONNECT SESSION"权限, 加上

grant debug connect session to a;

4. 会话B设置调试参数
关联上被调试会话A

conn a/a
set pages 50000 line 130
set serveroutput on size unlimited
prompt Enter debug session id (from dbms_debug.initialize call):
accept ssid
exec dbms_debug.attach_session('&ssid')

...
SQL> prompt Enter debug session id (from dbms_debug.initialize call):
Enter debug session id (from dbms_debug.initialize call):
SQL> accept ssid
0661078B0001
SQL> exec dbms_debug.attach_session('&ssid')

PL/SQL procedure successfully completed.

SQL>

输入上一步得到的会话标识

在第3行设置断点

-- Set a breakpoint at line 3 (must be an 
-- executable statement)
declare
  info dbms_debug.program_info;
  bnum binary_integer;
  ret binary_integer;
begin
  info.namespace := dbms_debug.namespace_pkgspec_or_toplevel;
  info.name := 'TESTPROC';
  info.owner := USER;
  info.dblink := null;
  info.line# := 3; 

  ret := dbms_debug.set_breakpoint(info,3,bnum);
  if ret != dbms_debug.success then
    dbms_output.put_line('Failed to set break point');
  end if;
end;
/

5. 会话A运行程序

exec testproc

程序挂起


6. 会话B获取调试结果, 然后继续会话A,直到存储过程结束

declare
  info dbms_debug.runtime_info;
  ret binary_integer;
  source varchar2(4000);
  -- Set runtime information mask since default 
  -- info is only info_getStackDepth + 
  -- info_getLineInfo
  mask pls_integer := 
    dbms_debug.info_getstackdepth +
    dbms_debug.info_getbreakpoint +
    dbms_debug.info_getlineinfo +
    dbms_debug.info_getoerinfo;
  -- Set breakflags to step to next executable
  -- statement
  breakflags pls_integer := dbms_debug.break_next_line;
begin
  -- Start the interpreter
  ret := dbms_debug.synchronize(info,0);
  if ret != dbms_debug.success then
    dbms_output.put_line('Synchronize failed');
  end if;
  if info.reason = dbms_debug.reason_interpreter_starting 
  then
    dbms_output.put_line('Interpreter starting');
  end if;

  -- Continue to break point
  ret := dbms_debug.continue(info,0,mask);
  if ret != dbms_debug.success then
    dbms_output.put_line('Continue failed');
  end if;

  -- Output info returned
  dbms_output.put_line('Line is '||info.line#);
  if info.terminated = 1 then
    dbms_output.put_line('Program has terminated');
  end if;
  dbms_output.put_line('Breakpoint # is '||info.breakpoint);
  dbms_output.put_line('No. of frames on stack is '||info.stackdepth);
  if info.reason != 3 then
    dbms_output.put_line('Program suspended but not for breakpoint');
  end if;
  dbms_output.put_line('Program name is '||info.program.name);
  -- Find the line of code the break is on
  select text into source from all_source
   where owner = info.program.owner
     and name  = info.program.name
     and line  = info.line#;
  dbms_output.put_line('Source code is '||chr(10)||source);
  if info.oer != 0 then
    dbms_output.put_line('Exception is '||sqlerrm(info.oer));
  end if;

  -- Step to next line and print it out
  ret := dbms_debug.continue(info,breakflags,mask);
  if ret != dbms_debug.success then
    dbms_output.put_line('Continue failed');
  end if;
  dbms_output.put_line('Line is '||info.line#);
  select text into source from all_source
   where owner = info.program.owner
     and name  = info.program.name
     and line  = info.line#;
  dbms_output.put_line('Source code is '||chr(10)||source);

  -- Continue to end of program now
  ret := dbms_debug.continue(info,0,0);
  if ret != dbms_debug.success then
    dbms_output.put_line('Continue failed');
  end if;
end;
/

输出的调试结果

Interpreter starting
Line is 3
Breakpoint # is 1
No. of frames on stack is 2
Program name is TESTPROC
Source code is
  update tt


Line is 6
Source code is
  dbms_output.put_line('Rows: '|| sql%rowcount);



PL/SQL procedure successfully completed.

7. 会话A关闭调试

exec dbms_debug.debug_off

挂起


8. 会话B结束调试

-- Need this since debug_off waits to be debugged 
-- (Enhancement Request, Bug 690796)
declare
  info dbms_debug.runtime_info;
  ret binary_integer;
begin
  ret := dbms_debug.synchronize(info,0);
  ret := dbms_debug.continue(info,0,0);
end;
/

运行完synchronize,continue后,前面的会话A才能退回到SQLPLUS提示符, 这步也可以在会话A关闭调试之前操作

exec dbms_debug.detach_session

最后释放与会话A的连接




外部链接:
DBMS_DEBUG
dbms_debug package - reg", version 8.1.7.0
PACKAGE DBMS_DEBUG Specification

metalink上的其它例子
DBMS_DEBUG: Simple example
DBMS_DEBUG: Simple Example When Using Scripts With SQL*Plus 8.1.7
这2个是一样的

调试匿名块:
DBMS_DEBUG: Simple Example Of Debugging An Anonymous Block

一个更好的例子在Debugging PL/SQL with DBMS_DEBUG,对DBMS_DEBUG做了一些封装


DBMS_DEBUG比较难用, 建议使用图形化的工具,如SQL Developer,PL/SQL Developer,TOAD等,进行调试





-fin