MyODG
Debugging PL/SQL Code

Debugging PL/SQL

You can debug

• An Anonymous PL/SQL code
• A Procedure or a Function
• An SP in a Java Application

As the screen shots explain it is very easy to debug a code that is run using this tool.
Also it is possible to debug an SP that is embedded in a Java application. For this choose the option to use Session ID.

Re compile procedures with debug option

For getting debug information for variable values it is required
to compile the target procedure or function with debug option

SQL> alter procedure   procedure_name  compile debug;

Debugging An SP in another Application or run from SQL*Plus

After connecting to database, Enter the Debug session ID and also timeout value in seconds

To obtain Debug session ID, start SQL session ( in sql*plus or toad ) and type the following 

Initialize the target session and set DEBUG ON

SQL> set serveroutput on
SQL> select DBMS_DEBUG.INITIALIZE from dual;

INITIALIZE
--------------------------------------------
0009006E0001

Now set DEBUG ON

SQL> set serveroutput off
SQL> exec DBMS_DEBUG.DEBUG_ON;

PL/SQL procedure successfully completed.

SQL> 

Debugging An SP in Java Application

After connecting to database, Enter the Debug session ID and also timeout value in seconds

To obtain Debug session ID, modify the java application to execute the following SQL statements

1) select DBMS_DEBUG.INITIALIZE from dual;
The output has to be printed to screen. This is the Debug ID

2) Run the SP DBMS_DEBUG.DEBUG_ON;

Now enter the Debug ID int opening screen and press "start debug".
Another screen will appear and now press synchronize button and start the procedure from Java