PL/SQL Debugger

[Kumud]

How to debug PL/SQL store procedure when there is no debugger.
I created procedure, no compilation error. Runs successfully.
But it does not do any thing. It is not showing me DBMS_OUTPUT mesage also.
How can I debug code and find out what's wrong without using any tools?
Does oracle has pl/sql debugging tools.
Or do you know any Cheap PL/SQL Debugger tools ?
No TOAD (it's expensive)

[marist89]

Originally posted by Kumud
It is not showing me DBMS_OUTPUT mesage also.

Did you:
SET SERVEROUTPUT ON

There are lots of pl/sql debuggers out there. Personally, I think TOAD is best. Compare the cost of TOAD to 10 hours of your time and then decide if it is expensive.

Oracle Developer comes with a PL/SQL debugger module. At 4K+ per SEAT, now we're talking expensive...

[sambavan]

http://www.oraclepower.com/isapi/ora...SC&CAT_ID=1100


Sam

[gpsingh]

Profiling: Identifying and isolating performance problems in applications that consist of large numbers of PL/SQL packages is difficult at best, requiring extensive
detailed analysis of the code. Oracle8i's new profiling API, implemented through
the DBMS_PROFILER PL/SQL package, provides functions for gathering
statistics, such as the total number of times each line has executed, time spent
executing each line, and minimum and maximum amount of time spent on executing
a given line of code. The statistics create a "profile" of the application.
For example, developers can generate profiling information for all named library
units used in a single session. This information is stored in database tables you can
query to obtain information on time spent in each PL/SQL line, module, and
routine.

Debugging: Oracle8i also includes the DBMS_DEBUG API, which has several
advanced debugging features, including the ability to debug PL/SQL code on the
client and server (with the Oracle Developer tools), step through code, set
breakpoints, examine and modify values of variables, stop on exception, execute
arbitrary code at a breakpoint, and examine cursor state.
Several debuggers that use the DBMS_DEBUG API are already available from
Oracle and third-party suppliers. Oracle Procedure Builder is an advanced
client/server debugger that can transparently debug Oracle Developer and database
applications.

Tracing: If a debugging tool isn't available or if an application is too complex to
debug, developers can use the new DBMS_TRACE API to trace programs by
function or by exception, which can be very useful during all phases of
development, especially while debugging complex PL/SQL applications.

[Kumud]

Thanks gpsingh, I need to find out how to use dbms_debug and dbms_profiler.