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 generates.

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, mytable(i*10). 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 database:

• PL/SQL Objects You Can Debug With JDeveloper
• About Debugging Triggers, Java Stored Procedures, and Oracle Object Types
• Appearance of Debug Information in Supported Oracle Databases
• Known Issues
• Notes

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:

• Stand-alone procedures
• Stand-alone functions
• Packaged procedures
• Packaged functions

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

• 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 procedure.

Appearance of Debug Information in Supported Oracle Databases

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 (DBMS_DEBUG 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 used.

Known Issues

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 including:

CURSOR

The value displayed will show the flags, rowcount, and knlflags.

RECORD

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, my_rec.my_field.

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 tables.

Notes

• 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:

  DBMS_DEBUG_JDWP.CONNECT_TCP( <host_name>, <port> )

  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 or V_Value. Package variables are also case-insensitive, but the prefix leading up to the variable name is case-sensitive; for example:

  $Oracle.Package.SCOTT.MY_PACKAGE.g_var

  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.

Related topics

About Debugging Remote PL/SQL Programs

Moving Through Code While Debugging

Copyright © 1997, 2004, Oracle. All rights reserved.