About Debugging PL/SQL Programs and Java Stored Procedures
JDeveloper supports both PL/SQL and Java stored procedures debugging in
a single IDE tool. When debugging PL/SQL, the source code you are
debugging must be stored in the Oracle database. For Java stored
procedures, the source code should be in your JDeveloper project and the
compiled code should be deployed in the database.
Also, the way the debug action is initiated is different depending on
whether you are performing local or remote debugging. When debugging
PL/SQL, this distinction is described as follows:
Local debugging - JDeveloper automatically launches
the program you want to debug, also referred to as the debuggee
process, and then attaches the debugger to that program.
Remote debugging - You must manually launch the
program you want to debug with an Oracle client such as SQL*Plus,
Dbms_Job, an OCI program, or a trigger firing. You must then establish
the connection from the database debuggee process to the JDeveloper
debugger. Once the debuggee is launched and the JDeveloper debugger is
attached to it, remote debugging is very similar to local debugging.
PL/SQL and Java stored procedure debugging information is displayed in
the various JDeveloper debugger windows
including the Smart Data, Data, Watches, Inspector, Stack, and Classes
windows. The Threads window, Heap window, and Monitors window are not
applicable when debugging PL/SQL code. Press F1
or click Help in the debugger window
for additional notes regarding debug information that the debugger
When debugging PL/SQL, the user can use PL/SQL expressions in the
Watches and Inspector windows as well as conditional breakpoints,
including table element access; for example,
This capability includes tables which are declared in functions,
procedures, packages, and package bodies.
This topic contains information which applies to debugging both local
and remote PL/SQL programs and Java stored procedures in the Oracle
PL/SQL Objects You Can Debug With JDeveloper
You can debug a PL/SQL program calling PL/SQL, PL/SQL calling a Java
stored procedure (Oracle9i Release 2 database only), and a
PL/SQL program issuing a SQL statement that fires a trigger.
You can initiate debugging PL/SQL from the following objects:
Any other PL/SQL object can be traced into as long as it meets the
prerequisites, and as long as it is invoked from one of the above.
About Debugging Triggers, Java Stored
Procedures, and Oracle Object Types
Consider the following when debugging triggers, Java stored procedures,
and Oracle object types:
Although you cannot initiate debugging for these objects, you can land
in them. For example, you cannot start debugging a trigger, but you
can debug a procedure that adds records. To debug a trigger, set a
breakpoint in the trigger, then debug the procedure that causes the
trigger to fire. The debugger will stop at that breakpoint.
Debugging and stepping into Java stored procedures is only supported
in the Oracle9i Release 2 database. Also, these procedures
should be included in the JDeveloper project and the source should be
consistent with what is deployed in the Oracle database. To debug a
Java stored procedure, set a breakpoint in the Java stored procedure,
then debug the PL/SQL that calls the Java stored procedure.
Alternatively, you can debug the PL/SQL and step into the Java stored
Appearance of Debug Information in Supported
When debugging PL/SQL code residing in the Oracle9i Release 2
database or later, the debugger uses the database's new JPDA (Java
Platform Debugger Architecture) implementation. JPDA is the industry
standard for Java debugging and the JPDA implementation in the database
allows you to seamlessly debug Java and PL/SQL. For debugging code
residing in the Oracle8i or Oracle9i Release 1
databases, the debugger uses a different API (
package) which provides less detail in the debugger windows.
Depending on your database version, the debug information from PL/SQL
code displays differently in the JDeveloper debugger windows. However,
editing PL/SQL code in the JDeveloper Code Editor is the same regardless
of database version.
Differences Between Debugging on Oracle8i or Oracle9i
Release 1 and Oracle9i Release 2 Databases
The main differences between debugging on the Oracle8i Release
8.1.7 or Oracle9i Release 1 databases and debugging on the
Oracle9i Release 2 database are as follows
Debugging Java stored procedures is supported in the Oracle9i
Release 2 database only.
For Oracle8i or Oracle9i Release 1 databases, scalar
data types such as NUMBER and
VARCHAR2 are displayed as a single, nonexpandable entry in the Data and
Watches window. For Oracle9i Release 2 databases, all data
types (including scalars) are shown as expandable entries.
For Oracle8i or Oracle9i Release 1 databases,
limited access to composite data types is provided. See "Known Issues"
below for more information.
For Oracle8i or Oracle9i Release 1 databases, the
Tracing Classes and Packages to Include and the Tracing Classes and
Packages to Exclude entries, which are specified in
Tools | Project
Properties - Debugger, are not
The following are issues encountered when debugging against an Oracle8
i or Oracle9i Release 1 database. These are limitations of the
older API and will not be fixed.
Composite data types
The JDeveloper data-related windows including the Data window, the
Watches window, and the Inspector windows can display variables which
are scalar types such as NUMBER,
VARCHAR2, and the like. Other more complex types are also displayed
The value displayed will show the flags, rowcount, and knlflags.
The debugger cannot display an entire record. However, you can enter
an expression in the Watches or Inspector windows to see a given field
in the record. For example,
TABLE of <scalar>
The debugger displays a folder (similar to how it displays an array in
Java stored procedures). The user can expand the folder to see the
elements. The user can right-click the folder and choose
Adjust Range to enter the range of indexes that should be displayed
when the folder is expanded. The default range is 1...100. The more
indexes in the range (even if the elements for those indexes don't
exist in the table), the longer it will take to expand the folder.
TABLE of <composite>
The debugger displays a folder (similar to how it displays an array in
Java stored procedures). If the user expands the folder, the debugger
may not be able to display the elements. This includes tables of
If you want to configure the debugging behavior (for remote debugging
or for setting the Classes Include and Exclude lists), you must have
an active workspace and project to access the project's debugger
settings in the T
ools | Project Properties - Debugger page.
For Oracle9i Release 2 database, the following command is
used to connect the debuggee session to the debugger:
For local debugging, JDeveloper issues this command for you. For
remote debugging, you will need to issue this command in the same
session that you use to call the PL/SQL you want to debug.
When entering an expression in the Watches window, local variables can
be entered in any case; for example,
V_Value. Package variables are also case-insensitive, but the
prefix leading up to the variable name is case-sensitive; for example:
The simplest way to add a package variable to the Watches window is
to drag and drop the variable from the Data Window or to drag and
drop the package from the Classes Window.
About Debugging Remote PL/SQL Programs
Moving Through Code While
Copyright © 1997, 2004, Oracle.
All rights reserved.