使用dbms_debug包调试存储过程

izuoyan

浏览: 9197359 次
性别:
来自: 上海

最近访客更多访客>>

GDGZWQZ

morelily

eternal1025

devcang

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2012-06 ( 32)
2012-05 ( 145)
2012-04 ( 50)
更多存档...

SQL Oracle Access

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

----------------------------------------------------

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;

3begin

4 select pkg_dbgd.tst_1(4) into v_result from dual;

5end;

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;

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;

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

分享到：

SOA不是管理方法最重要 | uusee花4000万买个奥运转播权

2008-07-10 11:38
浏览 853
评论(0)
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论