Database and PL/SQL Development in Oracle9i JDeveloper:
A Technical Overview

Overview

Oracle9i JDeveloper is an Integrated Development Environment providing end-to-end support for building, testing, and deploying J2EE applications, Web Services, and PL/SQL. Oracle9i JDeveloper is written entirely in Java, and, as such, is a multi-platform development environment. Currently, JDeveloper is tested and supported on Windows, Linux, Solaris, and HP/UX.

This document describes the features for PL/SQL development and general database development that are included with Oracle9i JDeveloper 9.0.3. The many JDeveloper features for building applications that access the database (for example, Business Components for Java, JPublisher, SQLJ, publishing PL/SQL as a web service, etc) are not in the scope of this document.

• Database Development
  • Managing Connections
  • Database Browsing
  • Schema Object Creation
  • Registering Third-Party Drivers
  • Executing Statements in SQL Worksheet
• PL/SQL Development
  • Running PL/SQL Procedures, Functions, and Packages
  • Editing PL/SQL in the Code Editor
  • Getting more information from the Navigator and Structure windows
• PL/SQL Debugging
  • PL/SQL Debugging Features
  • Additional PL/SQL Debugging features for Oracle9i Release 2
  • Setup Requirements for PL/SQL Debugging
  • Remotely Debugging PL/SQL
• Features for Future Releases

Database Development

Managing Connections

Oracle9i JDeveloper allows you to create stored database connections using a simple wizard interface. These connections can be used for browsing the database, creating schema objects, executing and tuning ad-hoc SQL statements, identifying connections in other wizards, and deploying server-side code.

Figure 1: The Connection Wizard

Database Browsing

Using a database connection created in the Wizard, users can browse through the objects in a database. The database browser is implemented with JDBC and thus allows browsing of any database with a JDBC driver. Browsing an Oracle database yields access to additional database objects not available with other databases.

One of the key aspects of browsing the database is the Table Viewer. In the Table Viewer, you can see at a glance details about columns and constraints for a table, as well as quickly retrieve the data from the table for further examination. This is done in a scalable manner where (by default) just the first 100 rows are fetched, and you can manually fetch subsequent rows if needed.

Figure 2: Browsing the database with the Table Viewer

Schema Object Creation

JDeveloper supports the creation of any schema object by executing a SQL statement in the SQL Worksheet. Assistant tools are provided for a subset of schema object types. JDeveloper 9.0.2 provides assistant tools for creating database users and PL/SQL programs (procedures, functions, packages). JDeveloper 9.0.3 expands the set of assistant tools for schema object creation to include tables, views, and triggers.

Figure 3: Creating constraints in the Table Wizard.

Registering Third-Party Drivers

The process for accessing a data source using third-party driver has been dramatically simplified in Oracle9i JDeveloper 9.0.3. You can now register drivers so that they can be easily reused.

To register a third-party driver:

1. Create a new Database Connection.
2. In Page 1 of the wizard, select Third Party JDBC Driver as the Connection Type.
3. In Page 3 of the wizard, click New to register a new driver.
   1. In the resulting dialog, enter the full name of the JDBC driver, for example:
      com.microsoft.jdbc.sqlserver.SQLServerDriver
   2. Press New to define a new library which references the location of the JDBC driver.
4. Enter the URL for this connection.

You've now registered a driver that you can easily reuse for new connections and a library containing the driver that you can add to projects using the driver. You can manage the registered drivers from a central location (Tools > Preferences, Database Connections).

Note that this is intended mostly for Type IV (all-Java) drivers. You may wish to register a Type II driver in the same way to take advantage of the library creation and reusable connection name, but due to complications with dynamically loading .dll files, Type II drivers must be in the system path or specified in the jdev.conf file.

Executing Statements in SQL Worksheet

Oracle9i JDeveloper supplies a SQL Worksheet for executing and tuning SQL statements. It provides quick access to commands to either execute or get the explain plan for the selected statement. Previously executed statements can be recalled using the History button.

Figure 4: Viewing the explain plan of a statement in the SQL Worksheet.

PL/SQL Development

Running PL/SQL Procedures, Functions, and Packages

You can run PL/SQL procedures, functions, and packages by simply right-clicking on the object in the Navigator and choosing Run <plsql_object_name>. You will be presented with a dialog showing details about the arguments and, for functions, return values for the selected object. If the selected object is a package, the dialog will display a list of the procedures and functions defined in the package spec. You can select one of these procedures or functions as the target you want to run.

When you invoke the Run PL/SQL dialog, code is automatically generated to call the target PL/SQL program unit. You can modify this code directly in the dialog to intialize and pass parameters. Additionally you can save the modified code for reuse in subsequent runs of that program. (Note, available in the production version.)

When you run a PL/SQL program that makes calls to DBMS_OUTPUT, the results will be displayed in the Log window. Likewise, return values from functions, and values of OUT parameters are also displayed in the Log window.

Figure 5: Running a function in a package.

Editing PL/SQL in the Code Editor

JDeveloper includes a full-featured editor for PL/SQL program units, including customizable PL/SQL syntax highlighting in addition to common editor functions such as Bookmarks, Macros, Code Templates, Search and Replace, etc. PL/SQL Code Insight is also available from the Editor. For example, if you type DBMS_OUTPUT. followed by Ctrl+Space, you can select from a list of members of that package. Similarly, when typing a SQL statement, you can type EMP. followed by Ctrl+Space to invoke a list of columns in that table. Note that by default, Code Insight will be invoked automatically (without pressing Ctrl+Space) if you pause after typing a period (".") for more than one second.

When using the Code Editor to edit PL/SQL code, you can Make, Rebuild, or Save (all of these actions have the same result for PL/SQL) your work which sends the source code to the database for recompilation and storage of the program. Any syntax errors encountered during compilation will be displayed in the Log window. You can navigate to the source of a syntax error by double-clicking the error message in the Log window.

Figure 6: Editing PL/SQL in the Code Editor.

Getting more information from the Navigator and Structure windows

The Navigator and Structure windows in JDeveloper provide additional information that will be useful for PL/SQL developers. For example, invalid PL/SQL objects are shown in the Navigator with a red "X" overlay icon, as in the following icon for an invalid function: . (Note that new for JDeveloper 9.0.3, package bodies and object bodies are listed under their respective specifications.)

For some database object types, the Structure window displays additional information about the currently selected object. The following table identifies the additional details provided by the Structure window for various object types.

Figure 7: The contents of a package as seen in the Structure window.

PL/SQL Debugging

Oracle9i JDeveloper 9.0.2 provides a preview of PL/SQL debugging in JDeveloper. The preview version is undocumented and unsupported and is available only with Oracle9i Release 2 (Oracle 9.2) databases. Oracle9i JDeveloper 9.0.3 provides full support for PL/SQL debugging with Oracle8i, Oracle9i and Oracle9i Release 2 databases.

PL/SQL Debugging Features

JDeveloper 9.0.3 contains a full-featured debugger for PL/SQL. Highlights of the PL/SQL debugging features include:

• Control program execution: The PL/SQL debugger in JDeveloper supplies many commands to control program execution including Step Into, Step Over, Step Out, Run to Cursor, Pause, Resume, and Terminate.
• View and modify variables: While the debugger is paused, you can examine and modify the values of variables from the Smart Data, Data, Watches or Inspector windows. For PL/SQL collections, you can adjust the range of visible values in the debugger.
• Customizable breakpoints: JDeveloper breakpoints are highly configurable. For PL/SQL debugging, you can use source breakpoints (associated with a particular line of executable code in a particular program unit) and exception breakpoints (associated with any unhandled exception, or  a specific Oracle exception). You can define conditional breakpoints for PL/SQL and customize the action of breakpoints, for example, pause the debugger (default), beep, log occurence to a text file, or enable or disable other breakpoints.

PL/SQL debugging Information is available from several windows in JDeveloper. The following list provides examples of the kind of information available during debugging.

• Code Editor: Shows the execution point. Flyover tooltips display the name and value of the variable under the pointer.
• Breakpoints window: Lists the defined breakpoints. You can use this window to add new breakpoints, or customize the behavior of existing breakpoints.
• Data window: Displays all variables that are currently in scope, including package variables, package body variables, variables declared in the current procedure or function, and local variables (such as those declared in a nested block or implictly declared).
• Smart Data window: Displays only the variables referenced in the line of code about to be executed and in the previous two locations. Note that this is customizable. For example, you might want the Smart Data window to show the variables used in the line of code about to be executed and the next two lines, and to retain variables used in the previous four locations.
• Watches window: Displays expressions or variables you've added to the Watches window by either selecting Add Watch from the context menu of the window and entering the expression, or by dragging a variable from one of the other windows (such as the Data or Smart Data windows) to the watches window.
• Inspector windows: Display expressions or variables you've added to the Inspector window. This is similar to the Watches window, except that Inspector windows float by default and only one expression is displayed in each Inspector window.
• Stack window: Shows the execution stack. For example, if procedure PROC_A calls PROC_B which in turn calls FUNC_C, when the execution point is in FUNC_C, the Stack window will display something like the following (JDEV_TMP_PROC_1 represents the temporary procedure created by JDeveloper to run PROC_A):

  

• Figure 8: Stack window showing FUNC_C called by PROC_B called by PROC_A.

  Note that you can use the Stack window to change the context in the debugger. In other words, in the above example, if you double-click on PROC_B in the Stack window, the debugger windows (and Code Editor) will update to show data relevant to PROC_B.

• Classes window: Shows a list of PL/SQL programs and Java classes that have been loaded in this database session.

Additional PL/SQL Debugging features for Oracle9i Release 2

PL/SQL debugging in JDeveloper is implemented in two distinct ways. For debugging against database versions prior to Oracle9i Release 2 (Oracle 9.2), JDeveloper uses the DBMS_DEBUG API provided by the server. Starting with Oracle9i Release 2, JDeveloper utilizes the new JDWP (Java Debugging Wire Protocol) implementation provided by the server. JDeveloper automatically detects which version of the database you are using for debugging and uses the appropriate method for that version.

Because of the variations in the two debugging implementations, you will see some differences when debugging with specific database versions. JDeveloper is able to take advantage of several features that are provided only with the JDWP implementation in Oracle9i Release 2:

• Debugging Java stored procedures: With JDeveloper and Oracle9i Release 2, you can debug Java stored procedures and PL/SQL programs seamlessly. For example, if you have a PL/SQL procedure that calls a Java stored procedure, you can step into the Java stored procedure call from the PL/SQL procedure. Or, you can set a breakpoint in the Java stored procedure then debug the PL/SQL procedure -- the debugger will pause when the breakpoint in the Java stored procedure is reached. Debugging Java stored procedures in database versions prior to Oracle9i Release 2 is not possible with JDeveloper.
• PL/SQL collections: The DBMS_DEBUG API has limited support for PL/SQL collections, such as tables, records, and cursors. Using JDeveloper to debug PL/SQL in an Oracle9i Release 2 database, you have complete access to composite PL/SQL structures. For example, if your PL/SQL program uses a PL/SQL table of records, you can expand the table object in the Data window to see the records, then expand a record to see the fields, then select a field and modify its value on the fly. It is possible to access composite PL/SQL structures in database versions prior to Oracle9i Release 2, but you must manually enter the fully qualified name of the element in the Watches window, for example, for a table named "tab" containing records with a field named "field",  tab(4).field.
• Debugging remotely: With the JDWP implementation, JDeveloper is able to leverage its remote debugging capabilities with server-side code. Remotely debugging PL/SQL involves starting the JDeveloper debugger listener, then attaching to that listener via the database session you want to debug. (See the section Remotely Debugging PL/SQL below.) Remotely debugging PL/SQL in database versions prior to Oracle9i Release 2 is not possible with JDeveloper.

Setup Requirements for PL/SQL Debugging

To enable PL/SQL debugging, several conditions must be met:

1. Any code you wish to debug by either setting a breakpoint or by stepping into, must be compiled with debug information.

   There are three ways to make sure that PL/SQL procedures, functions, packages, or triggers are compiled with debug information:

   • JDeveloper preferences: Check to ensure that the Generate PL/SQL Debug Information checkbox is checked in the Tools > Preferences, Database Connections page. Then use JDeveloper to create or recompile the code you plan to debug.
   • Session setting: From another client, such as SQL*Plus, you can modify your session using the command:

     ALTER SESSION SET PLSQL_DEBUG = TRUE

     Any PL/SQL program unit created or recompiled in that session from that point on will include debug information.

   • Object level: Alternatively, you can enable debugging on invidual program units using the command:

     ALTER <PROCEDURE | FUNCTION | PACKAGE | TRIGGER> <prog_unit_name> COMPILE DEBUG

   To compile Java stored procedures with debug information, compile the Java classes with debug information before loading them into the database. For example, from JDeveloper, make sure the Include Debug Information checkbox is checked in the Project | Project Settings, Compiler page. When deploying the Java class to the database, do not use the -resolve option which instructs the server to recompile the class.

2. Some additional prerequisites must be met depending on the database version:

   For Oracle8i and Oracle9i:

   • No special privileges are required to debug PL/SQL in your own schema, but to debug objects owned by other users, your user must have been granted the following privilege:

     CREATE ANY PROCEDURE

   For Oracle9i Release 2:

   • In order to debug with Oracle9i Release 2, your user must have been granted the following privileges:

     DEBUG ANY PROCEDURE
     DEBUG CONNECT SESSION

   • Additionally, code must be compiled in INTERPRETED mode (not in NATIVE mode). Compiling PL/SQL in NATIVE mode is new with Oracle9i Release 2. This is set in the init.ora file, and INTERPRETED is the default mode.

   

   Figure 9: Debugging a PL/SQL package with JDeveloper.

Remotely Debugging PL/SQL

Using JDeveloper to locally debug PL/SQL entails setting a breakpoint where you want the debugger to pause, then selecting a PL/SQL procedure, function, or package in the Navigator and pressing the Debug button. In this case, JDeveloper starts the debugging session, connects to it, and pauses when the breakpoint is reached, all without your intervention. Another way to think of it is that with local debugging, JDeveloper is the client that initiates debugging.

Using JDeveloper and Oracle9i Release 2, you can also remotely debug PL/SQL. Remotely debugging PL/SQL means that you initiate the debug action from a client external to JDeveloper, for example, a PL/SQL web application, an OCI program, a SQL*Plus session, etc. In this case, you must manually perform some steps that JDeveloper would otherwise do for you.

To remotely debug PL/SQL (assuming you have set a breakpoint, as you would for local debugging):

1. Start the JDeveloper debugger listener by selecting Project > Project Settings. In the Debugger, Remote panel, check the Remote Debug checkbox, and the Listen for JPDA radio button. Now press the Debug button and enter a port number for the debugger listener.

   Note: Remotely debugging PL/SQL requires a Workspace and Project to enable remote debugging. If you do not have a Workspace and Project, you will need to create them.

2. Attach to the JDeveloper debugger listener from database session using the following procedure call:

   DBMS_DEBUG_JDWP.CONNECT_TCP ('hostname_or_ip', port_number)

   where hostname_or_ip is the hostname or IP address of the machine running JDeveloper, and port_number is the port number you entered when starting the debugger listener.

3. Finally, from the same database session as is attached to the debugger listener, call the PL/SQL program that either contains the breakpoint or will cause the breakpoint to be reached. For example, from SQL*Plus:

   EXEC my_procedure;

Figure 10: Illustration depicting the steps to remotely debug PL/SQL.

Features for Future Releases

The JDeveloper product group is committed to expanding the level of database development and PL/SQL development support in future releases. We are working closely with internal organizations and customers to identify and prioritize our efforts in this area. While these features have not yet been targeted for a specific relese of JDeveloper, some of our planned enhancements for the near-term include

• Database schema modeling
• Find/Filter objects
• Edit data directly from the Table Viewer
• Export data
• Show and generate DDL for objects
• Browse and create more schema object types
• Show object dependencies
• Edit schema objects (for example, making the Table Wizard reentrant)

For More Information

For more information about Oracle JDeveloper, visit us on OTN at /products/jdev/ . There you'll find online demos, technical information and white papers, how-to documents and samples, and a JDeveloper discussion forum where you can ask technical questions and provide your feedback. From the same web site, you can also download the product and try it out for free!