This chapter describes how you can use JDeveloper to write methods in Java for stored procedures and deploy them to the database.
This chapter includes the following sections:
Section 29.1, "About Running and Debugging PL/SQL and Java Stored Procedures"
Section 29.2, "Running and Debugging Functions, Procedures, and Packages"
Section 29.3, "Debugging PL/SQL Programs and Java Stored Procedures"
A Java stored procedure is a Java method that resides and runs in a database. Stored procedures can help improve the performance of database applications because they are efficient: they are stored in the RDBMS in executable form, and run in the RDBMS (rather than the client) memory space.
When you deploy a Java class to the database, you can select the methods that you want to publish to PL/SQL for use as stored procedures. Methods can be deployed together in a package or separately.
JDeveloper lets you run and debug PL/SQL program units. For example, you can specify parameters being passed or return values from a function giving you more control over what is run and providing you output details about what was tested.
Note:
The procedures or functions in Oracle Database can be either standalone or within a package.
To run or debug functions, procedures, and packages:
Choose View > Database > Database Navigator.
Expand IDE Connections or application, and select a database connection.
Expand a schema and expand the appropriate node depending on what you are debugging (Procedure, Function, or Package body):
Optional for debugging only) Right-click and choose Compile for Debug from the context menu of the node for the object that you are debugging. This compiles the PL/SQL program in INTERPRETED mode.
(Optional for debugging only) Select the function, procedure, or package that you want to debug and double-click to open it in the editor.
(Optional for debugging only) Set a breakpoint in your PL/SQL code by clicking to the left of the margin.
Note:
The breakpoint must be set on an executable line of code. If the debugger does not stop, the breakpoint may have not been set on an executable line of code (verify that the breakpoint was verified).
Make sure that either the editor or the procedure in the navigator is currently selected.
Click Debug, or if you want to run without debugging, click Run.
The Run PL/SQL dialog is displayed.
Select a Target which is the name of the procedure or function that you want to debug. Notice that the content in the Parameters and PL/SQL Block boxes change dynamically when the target changes.
Note:
You will have a choice of target only if you choose to run or debug a package that contains more than one program unit
The Parameters box lists the target's arguments (if applicable).
The PL/SQL Block box displays code that was custom generated by JDeveloper for the selected target. Depending on what the function or procedure does, you may need to replace the NULL values with reasonable values so that these are passed into the procedure, function, or package. In some cases, you may need to write additional code to initialize values to be passed as arguments. In this case, you can edit the PL/SQL block text as necessary.
Click OK to execute or debug the target.
Analyze the output information displayed in the Log window. In the case of functions, the return value will be displayed. DBMS_OUTPUT messages will also be displayed.
In addition to debugging Java programs, the JDeveloper debugger enables you to debug PL/SQL programs and Java stored procedures in Oracle Databases.
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 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. After 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.
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.
You can debug a PL/SQL program calling PL/SQL, PL/SQL calling a Java stored procedure (Oracle9i Release 2 and later databases), 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. For more information, see Section 29.3.3, "Debugging PL/SQL and Java Stored Procedures Prerequisites."
Consider the following when debugging triggers, Java stored procedures, and Oracle object types:
Although you cannot initiate debugging for these objects, you can step into 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 supported in the Oracle9i Release 2 and later databases. These procedures should be included in the JDeveloper project and the source should be consistent with what is deployed in 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.
The debugger uses the database's 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.
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 application and project to access the project's debugger settings in the Application > Project Properties - Run/Debug/Profile page.
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.
When the database debugger is running, for example to debug PL/SQL through a database connection, the ports used are randomly assigned. This can cause problems with firewalls, and to avoid them you can edit the ide.properties file to ensure that a specific port is used.
If necessary, close JDeveloper.
In a text editor, open jdev_install/jdeveloper/jdev/system/oracle.jdeveloper.release_number/ide.properties.
Type the following:
DatabaseDebuggerPortOverride=port_number
where port_number is the port number you want the debugger to use.
Save ide.properties. When you restart JDeveloper, the port you specified will be used.
You can debug PL/SQL and Java stored procedures in JDeveloper.
Refer to the appropriate section below for additional information.
Ensure that the following prerequisites have been met before performing PL/SQL debugging:
Your database user account must have these privileges:
DEBUG ANY PROCEDURE
DEBUG CONNECT SESSION
The PL/SQL code must be compiled in INTERPRETED mode. You cannot debug PL/SQL code that is compiled in NATIVE mode. You set this mode in the database's init.ora file. See Oracle Database documentation for more information about this file.
If you do not have an active application and project, the debugger will use the properties defined in the Default Project Properties dialog, available from the Application menu. However, it is recommended that you create a application and a project that you will use when you debug PL/SQL. In the Launch Settings page of the Edit Run Configuration dialog (Edit button on the Run/Debug/Profile page of the Project Properties dialog, which is available from the Application menu), you should ensure that the Attempt to Run Active File Before Default check box is selected (default setting). This will instruct the debugger to run the active file (for example a PL/SQL procedure selected in the navigator or open the active file in the editor) when you start debugging.
PL/SQL objects must be compiled with the DEBUG option enabled. Choose one of these techniques to accomplish this task:
Ensure that Generate PL/SQL Debug Information is selected in Database Connections page of the Preferences dialog (available from the Tools menu), then create or recompile the objects you want to debug.
In SQL*Plus, execute ALTER SESSION SET PLSQL_DEBUG = true, then create or recompile the object you want to debug.
In SQL*Plus, execute ALTER <procedure, function, package> <name> COMPILE DEBUG;
Ensure that the following prerequisites have been met before performing Java stored procedures debugging:
The Java code must be deployed to the database and compiled with debug information. From JDeveloper, make sure the Include Debug Information check box is selected in the Compiler page of the Project Properties dialog (available from the Application menu), then deploy the Java stored procedure.
To step through a Java stored procedure, the Java source must be available in your JDeveloper project and must be consistent with what is deployed to the database.
When locally debugging PL/SQL programs, the call to initiate debugging is made directly from within JDeveloper. JDeveloper automatically launches the program you want to debug, also referred to as the debuggee process, and then attaches the debugger to that program.
Make sure that you've completed the prerequisites listed above.
To locally debug a PL/SQL program in JDeveloper:
Choose View > Database > Database Navigator.
Expand IDE Connections or application, and select a database connection.
Expand a schema and expand the appropriate node depending on what you are debugging: Procedure, Function, or Package Body.
Select the procedure, function, or package that you want to debug and double-click to open it in the editor.
Set a breakpoint in the PL/SQL code by left-clicking in the margin.
Note:
The breakpoint must be set on an executable line of code. If the debugger does not stop, the breakpoint may have not been set on an executable line of code (check that the breakpoint was verified). Also, verify that the debugging PL/SQL prerequisites were met. In particular, make sure that the PL/SQL program is compiled in INTERPRETED mode.
Make sure that the PL/SQL program unit you want to debug is currently selected in the Navigator.
Click the Debug toolbar button.
JDeveloper halts the execution at the first breakpoint (providing that this was set in the Start Debugging Option in the Project Properties dialog) and displays the state in the debugger windows.
Look at the debug information displayed in the JDeveloper debugger windows. For more information, see Section 19.6, "About the Debugger."
Resume debugging the PL/SQL program until you are satisfied.
The main difference between remote debugging and local debugging PL/SQL programs is how you start the debugging session. For 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 program you want to debug (debuggee) to the JDeveloper debugger. After the debuggee is launched and the JDeveloper debugger is attached to it, remote debugging is very similar to local debugging.
You can use the debugger with PL/SQL programs and Java stored procedures in Oracle Database.
Make sure that you've completed the documented prerequisites, listed in Section 29.3.3, "Debugging PL/SQL and Java Stored Procedures Prerequisites."
To remotely debug a PL/SQL program using JDeveloper:
If you don't already have one, create a database connection.
If you don't already have one, create a project.
In the Application Navigator, right-click the project and choose Project Properties.
Choose Run/Debug/Profile.
Either select an existing run configuration or create a new one, and click Edit.
In the Edit Run Configuration dialog, select PL/SQL and choose the database connection.
Select Tool Settings - Debugger - Remote and set the remote debugging preferences.
In the Database Navigator, right click the connection and chose Remote Debug.
In the Database Navigator, expand the Database node and navigate to the procedure, function, or package that you want to debug and double-click to open it in the source editor.
In the source editor, set a breakpoint in your PL/SQL code by left-clicking in the margin.
In the Application Navigator, right-click the project and choose Debug.
In the displayed dialog, enter the appropriate listening port number and click OK. You can choose any valid port number that is not in use by another process. In this example, the port number used is 4000.
Note:
If you want to bypass this dialog the next time you are debugging on this port, select the Save Parameters check box from this dialog.
In the Run Manager window, you should see which indicates that the debugger is listening for debugging connections.
Use an Oracle client such as SQL*Plus to issue the debugger connection command. Whatever client you use, make sure that the session which issues the debugger connection commands is the same session which executes your PL/SQL program containing the breakpoints.
For example, if you are using SQL*Plus, issue the following commands to open a TCP/IP connection to the designated machine and port for the JDWP session:
EXEC DBMS_DEBUG_JDWP.CONNECT_TCP( '123.456.789.012', '4000' )
where 123.456.789.012is the IP address or host name where JDeveloper is running, and 4000 is the port number on which the debugger is listening.
From this point on, when you make a call to the PL/SQL code containing the breakpoint, the JDeveloper debugger is activated.
When the debugger accepts a debugging connection, the new debugging process is reflected in the Processes folder in the Run Manager. Also, the Log window should display a message similar to the following:
Debugger accepted connection from remote process on port 4000.
In addition, notice that the layout in JDeveloper has switched from Design layout to Debugging layout (bottom-right of window). Also, the debugging windows including Stack, Data, and Watches, should now be visible.
In the Run Manager, an icon indicates that the port is continuing to listen and can accept multiple debugging connections.
Back in the Oracle client, issue a command which invokes the PL/SQL program unit containing your breakpoint. For example, in SQL*Plus, issue a command similar to the following:
EXEC FOO;
where FOO is the name of a PL/SQL procedure.
JDeveloper halts the execution at the first breakpoint (providing this was set in the Start Debugging Option in the Project Properties dialog, available from the Application menu) and displays the state in the debugger windows. For more information, see Section 19.6.5, "How to Set the Debugger Start Options."
Step into and resume debugging the PL/SQL procedure until you are satisfied. For more information, see Section 19.6, "About the Debugger."
When you are finished debugging, disconnect the debuggee using the disconnect command. For example, from SQL*Plus, enter:
EXEC DBMS_DEBUG_JDWP.DISCONNECT;
The following message appears:
Debugger disconnected from remote process.
To terminate the listening port, right-click the Run icon in the Run Manager and choose Stop Listening.
If you are debugging PL/SQL, then you can use PL/SQL expressions in the Watches window, Inspector window, Breakpoint conditions, and Breakpoint Log expressions.
The following table lists examples of acceptable legal PL/SQL expressions that you can use in the debugger.
Table 29-1 PL/SQL Expressions that can be used in the debugger
| PL/SQL Expression | Example |
|---|---|
|
Simple variable name |
|
|
Field Access |
|
|
Table element |
|
|
Comparison operation |
|
|
Arithmetic operation |
|
|
Logical operation |
|
|
Package variable name |
|
|
Fully-qualified Package name |
|
|
PackageBody variable name |
|
|
Fully-qualified PackageBody name |
|