Tools for Your .NET Space
By Christian Shay

Use Oracle Developer Tools for Visual Studio .NET with Microsoft Visual Studio 2005 for fast, easy Oracle development.

You're working on some C# or VB.NET routines that manipulate data you're retrieving from Oracle Database. You need to access the database to edit and debug some stored procedures, modify some data in a table, change the structure of a table or view, or perform one of countless other database tasks.

In the past, you've managed this by using a tool such as SQL*Plus or Toad, but you wish there were a toolset that would let you do all of this from directly inside Microsoft Visual Studio 2005. And you wish this toolset could include graphical designers and wizards that do the grunt work of figuring out the SQL that's needed for a particular database operation. And maybe this toolset could even automatically generate some of the C# or VB.NET data access code as well.

Your wishes have been granted: Oracle Developer Tools for Visual Studio .NET (ODT) makes all of this possible. This article gives you a quick tour of many of the features ODT has to offer. It also introduces the new fully integrated PL/SQL debugger and shows how to configure it for first use.

Oracle Developer Tools: Feature Listing

ODT is a tightly integrated add-in for Visual Studio. First released in June 2005, ODT is free and available for download from the Oracle Technology Network.

Major features of ODT include

  • Oracle Explorer
  • Designers and wizards
  • Oracle data window
  • Oracle PL/SQL editor
  • Integrated PL/SQL debugger
  • Automatic code generation
  • SQL Query Window
  • Integrated online help

Oracle Explorer: Viewing the Oracle Schema

When you need to find and examine a table, view, or other Oracle database object, the first place to go is to Oracle Explorer. To launch Oracle Explorer from the main menu, select View-> Oracle Explorer . Oracle Explorer, shown in Figure 1, is a tree control that lets you view the structure of the Oracle schema. You can view Oracle schema objects—including tables, views, packages, and stored procedures—here. When you select a schema object, the Visual Studio properties window displays metadata such as column datatypes or stored procedure parameter types.

figure 1
Figure 1: Oracle Explorer (left) and the PL/SQL editor/debugger (right)

A context menu for each schema object offers additional features: You can generate SQL scripts for schema objects, use the provided filters to limit what types of schema objects are shown, and run designers and wizards to alter the schema. For example, while viewing the structure of a table, you can use the context menu to run Oracle Table Designer to modify the design of the table.

Designers and Wizards: Simplifying Database Operations

ODT offers a variety of graphical designers and wizards to provide step-by-step help for creating or altering database objects, including tables, views, stored procedures, stored functions, PL/SQL packages, sequences, indexes, constraints, triggers, synonyms, and more.

To launch Oracle Table Designer, right-click the table in Oracle Explorer and select Design . Oracle Table Designer, shown in Figure 2, makes looking up datatypes or memorizing SQL syntax a thing of the past. You simply provide the table name and the names of the columns, select the appropriate datatype from the list, and click Save to create or alter the table. The newly created schema object then immediately appears in the Oracle Explorer tree control.

figure 2
Figure 2: Oracle Table Designer

Oracle Data Window: View and Modify Table Data

To quickly see table or view data, right-click the table or view in Oracle Explorer and select Retrieve Data . The Oracle data window, shown in Figure 3, appears; displays table or view data in an easy-to-read grid; and lets you insert, update, or delete data without leaving the Visual Studio development environment. When you modify a row, the cursor moves to a new row and the data is immediately committed. For large tables, you can provide a row number to navigate to a particular row.

figure 3
Figure 3: Oracle Data Window

PL/SQL Editor

To edit a procedure, function, or package, right-click the item in Oracle Explorer and select Edit . The PL/SQL editor appears, as shown in Figure 4, and provides a tightly integrated development environment: You don't need to leave Visual Studio to develop stored procedures. Standard Visual Studio features in the PL/SQL editor include syntax coloring to improve readability and collapsible regions to hide stored procedures or functions that are part of a large, complex package. This makes it easier to focus on the procedure or function you're working on. Additionally, as you enter SQL or PL/SQL statements, a list of tables or columns pops up to autocomplete the statement.

figure 4
Figure 4: The PL/SQL editor

To compile the PL/SQL code, right-click the item in Oracle Explorer and select Compile . Any errors found when you compile the PL/SQL are listed in the Visual Studio task list. Click the error text to go to the line of code that failed. Press the context-sensitive Help key to open the Error Messages and Codes manual for the error code in question.

To test complete stored procedures and functions, right-click the item in Oracle Explorer and select Run . A dialog box will appear, in which any required input parameters for the stored procedure or function can be entered. Click OK in the dialog box to execute the code. The results of running the stored procedure—the output parameters—are displayed in a final window, shown in Figure 5. Complex output types, such as REF CURSOR, are shown as links; click these links to open grids containing the complex data.

figure 5
Figure 5: Testing PL/SQL

Fully Integrated PL/SQL Debugger

A new feature introduced in ODT is a PL/SQL debugger that is fully integrated with Visual Studio. You can now debug the PL/SQL code inside stored procedures, functions, or triggers in exactly the same way you debug VB.NET or C# code. You can step through the PL/SQL code, view and modify variable values, look at the call stack, set break points, and more.

You can also step from your VB.NET or C# application code directly into PL/SQL code and back out again. For example, if your C# code is calling a PL/SQL procedure or function, using the Oracle Data Provider for .NET (ODP.NET), you can debug the C# code and then step into the PL/SQL code to continue debugging. You can examine all of the parameter values passed into the stored procedure or function, including complex datatypes and arrays from within the PL/SQL debugger. When the PL/SQL code execution has completed, you are returned to the C# code to continue debugging.

You can also set break points and debug PL/SQL stored procedures, functions, or triggers called by any application located on any platform without requiring any modification or rebuilding of that application. For example, a PHP application running on Linux calls the PL/SQL stored procedure FOO and passes in an array of values. After you set an environment variable in the environment of the PHP application, you can set a break point in FOO within Visual Studio and begin debugging when FOO executes.

Setting up PL/SQL debugging. ODT supports PL/SQL debugging in Oracle Database 9.2 or later on any platform. The developer must have DEBUG and EXECUTE privileges on the procedures or functions that will be debugged. To grant DEBUG privileges, the DBA could issue GRANT DEBUG ANY PROCEDURE TO USER . For Oracle Database 10g Release 1 (10.1) and later, the developer must also have DEBUG CONNECT SESSION permissions, for which the DBA could issue GRANT DEBUG CONNECT SESSION TO USER .

Oracle Database will connect to the PL/SQL debugger through a port on a TCP/IP connection. Because firewalls often restrict which ports may be used, you should set a valid port range on the Tools->Options->Oracle Developer Tools->PL/SQL Debugging page. This Options page lists all connections currently active in Oracle Explorer, so you must also choose a connection here to be used for debugging.

Before you debug the PL/SQL, the procedure, function, or trigger must be compiled for debug. To compile for debug, select the procedure in Oracle Explorer, right-click, and select Compile Debug .

Debugging the stored procedure or function. You can run a procedure (or function) directly from Oracle Explorer, by right-clicking the procedure name and selecting Step Into . If there are input parameters, they will be requested in a pop-up window and then debugging will commence.

To debug an application (built, for example, in C# code) from within Visual Studio that calls a PL/SQL stored procedure or function, first build the C# application for debug in the usual way. Then, in the Tools menu, check Application Debugging . Finally, set a break point in the PL/SQL code and begin debugging the C# code. When the stored procedure or function is called from C#, execution will stop at the break point in the PL/SQL code.

To debug PL/SQL stored procedures, functions, or triggers called by any application located on any platform, go to the Tools menu in Visual Studio and select Start Oracle External Application Debugger . When the dialog box appears, provide the number of a port over which Oracle Database can connect to Visual Studio. Then, in the environment of the external application, set the environment variable ORA_DEBUG_JDWP to the value host= machinename; port= XXX (no spaces allowed), where machinename is the machine where Visual Studio is located and XXX is the port number provided earlier. For example, if the application is on Windows, you might type the following from the command prompt: set ORA_DEBUG_JDWP=host=mypc;port= 1234 . Set a break point in Visual Studio in the PL/SQL you will be debugging. Run the external application, and execution will stop at the break point in the PL/SQL code. (Note that in order for you to debug PL/SQL stored procedures, functions, or triggers called by an application, the application must have been built with Oracle Database 9.2 or later client libraries.)

Automatic Code Generation

When you drag-and-drop a schema object from Oracle Explorer onto an application's form, code is automatically generated for SELECT, UPDATE, INSERT, and DELETE operations on that schema object. The code uses the OracleDataAdapter class, which is provided by ODP.NET. This class derives from the ADO DataAdapter class. Anyone who has worked with DataAdapter classes, even with other database vendors, will be familiar with this. You can then use the DataAdapter class to connect UI elements, such as DataGrid elements, on the application's form to Oracle Database—with minimal additional coding.

You can also generate code for a typed or untyped data set, based on an Oracle table or view. To generate the code, right-click the OracleDataAdapter icon on your form and select Generate Dataset . You can then bind this data set as a data source to UI elements or use it in other standard ways throughout Visual Studio, such as with the Visual Studio XML Schema designer.

SQL Query Window

Oracle Developer Tools for Visual Studio .NET

Oracle Developer Tools for Visual Studio .NET
Oracle Database 10g for Windows

VISIT .NET Developer Center

To open the SQL Query window, right-click your database connection (under the Data Connections node) in Oracle Explorer and select Query Window . To execute an ad hoc SQL statement, enter the query text in the SQL Query window and click Execute . If the output of the SQL statement is a table or view, you can choose text or grid output. You can highlight multiple statements and run them as a group. You can also use the script operator @ to run SQL scripts.

You can drag-and-drop schema objects from Oracle Explorer onto the SQL Query window surface to automatically generate the appropriate SQL to access that schema object.

Integrated Online Help

ODT includes key Oracle documentation that has been converted into Visual Studio help format, as shown in Figure 6. The SQL Reference , the PL/SQL User's Guide and Reference, Error Messages , and the Oracle Database Extensions for .NET manuals are included in ODT.

figure 6
Figure 6: Online help

You can read the chapters in these manuals sequentially via the Visual Studio help pane. In addition, this documentation has been integrated with the Visual Studio context-sensitive help. For example, while writing a stored procedure, you can highlight a SQL or PL/SQL keyword, such as SELECT, press the context Help key (usually F1), and be taken automatically to the page describing the syntax of the SELECT statement in the SQL Reference . The ODT documentation also contains helpful getting started and walk-through chapters, as well as a reference guide.


This article has presented major features of ODT, which makes .NET development with Oracle Database faster and easier. It has also provided the steps for configuring the new PL/SQL debugger.

Christian Shay ( ) is a principal product manager at Oracle.

Send us your comments