tracing procedures

[masiwan]

What tracing/Debugging tool isavailable for oracle procedures? I have written a simple procedure usin PL/SQL and I want to trace it so that I can see the source of errors.

 

[SantaMufasa]

Masiwan,

Native PL/SQL (to my knowledge) lacks industry-standard tracing/stepping tools to debug its code. I believe that there are other utility packages that provide PL/SQL code stepping/debugging, but nothing built-in in native PL/SQL.

The relatively quick and easy technique that I use for debugging both run-time and logic errors is to shuffle into my code statements that display progress through code or intermediate variable values:

set serveroutput on format wrap
declare
    x number := 0;
    procedure p (x varchar2) is dbms_output.put_line(x); end;
begin
    p('1');
    x := x + 1:
    p('2: x = '||x);
    <some other code>
    p('3');
    <more code>
    p('4');
end;
/

The "p" (print) statements display stepping and intermediate results that should help you diagnose your problem. Then, when you have diagnosed your problem, you can remove the "p" statements.

Let us know if my "poor man's" debugging technique provides a reasonable solution to your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Thargy]

Masiwan, Santa,

download a free copy of SQL Developer from Oracle's web site.

It provides a GUI for debugging (amongst a host of other things) and best of all, it's free!

Although TOAD's debugger is streets ahead of SQL developer, it's still a handy tool.

http://www.oracle.com/technology/oramag/oracle/06-may/o36sql.html

is where the necessary advice is to be found.

Regards

Tharg

Grinding away at things Oracular