Christian Shay - Oracle and .NET

[Update March 2015: This very popular blog post is quite a bit out of date after 9 years! It is no longer particularly feasible to use only one instance of Visual Studio for PL/SQL and ASP.NET debugging. We now recommend you use two instances of VS along with "External Application Debugging".

To get the latest details on how to use PL/SQL debugging with Visual Studio have a look at the Oracle Developer Tools for Visual Studio (ODT) online help. From Visual Studio, find the ODT help, find the "Oracle PL/SQL Debugger" section, then "PL/SQL Debugging Setup", then go through the"Debugging Setup Checklist". - CS]


Lots of you are using ASP.NET and the Oracle Data Provider for .NET to create web applications that access Oracle. When those applications call a PL/SQL stored procedure, wouldn't it be great to just step right into the PL/SQL and begin debugging with the live parameter values still intact?

As mentioned in my earlier blog entries, we now have a fully integrated PL/SQL debugger built right into Visual Studio which is included with the free Oracle Developer Tools for Visual Studio .NET (ODT).

Today I will explain how to configure Visual Studio and your ASP.NET project to get started debugging your PL/SQL stored procedures, functions and triggers from ASP.NET.

Background
Before I start - a brief word about the way Oracle's PL/SQL debugging architecture works. When you connect from any Oracle 9.2 or later client application, Oracle's client libraries check to see if a ORA_DEBUG_JDWP environment variable is set. If it is, the client passes this environment value along with the connect information (eg Oracle user/pass) to Oracle. This ORA_DEBUG_JDWP variable is set using the following format:

SET ORA_DEBUG_JDWP=host=hostname;port=portnum
where hostname is the machine where the PL/SQL debugger (eg Visual Studio) is located, and portnum is a TCP/IP port it is listening on. When Oracle database receives this information, it immediately attempts to connect to the debugger on the port number specified. If there is no listener running on the port, this connection will fail and an error will be returned to the client.

When you debug directly from Oracle Explorer in Visual Studio (manually typing in the parameter values for a SP and so on) ODT takes care of passing this host and port information for you. ODT also silently starts listening on the port. You aren't even aware that any of this this is happening. This is documented in the online help as "Direct Database Debugging".

Similarly, when you call PL/SQL from .NET code, all you need to do is turn on "Oracle Application Debugging" (a checkbox in the "Tools" menu). The Oracle Application Debugging checkbox sets the environment variable in the Visual Studio process space and starts up the debugger listener, all without the user having to know or care about these details. You just check it, and away you go, debugging from .NET code into PL/SQL and back out again. This is documented in the online help as "Oracle Application Debugging".

When you need to debug PL/SQL from ASP.NET, if you are using the "ASP.NET Development Web Server" which is the default IIS webserver testbed for Visual Studio 2005, things are still pretty simple. You can still use the "Oracle Application Debugging" checkbox, although with a couple very important caveats (see below).

If you have an independent IIS webserver instead, things get just a little bit more complicated. In this case, the IIS Web Server process is technically external to the Visual Studio process. This is no problem since ODT lets you call the SP's you want to debug from ANY external process located on ANY machine, as long as that ORA_DEBUG_JDWP environment variable is set. It just means that you need to set this environment variable yourself and start the listener yourself, rather than relying on ODT to do it for you automatically. This is documented in the online help as "External Application Debugging".

Setup Instructions - Using ASP.NET Development Web Server

Here are the setup Steps for when you are using the ASP.NET Development server (a built in IIS web server for Visual Studio 2005). For most people, this will probably be the way to go:

1) Perform the generic setup steps 1 through 9 from my earlier blog entry and test via Oracle Explorer ("Step Into") that at least Direct Debugging is working. You should skip step #7, which does not apply to ASP.NET projects. As per these steps, the "Oracle Application Debugging" checkbox should be checked, the Options page should be configured and breakpoints should be set.

2) If the ASP Development Server is running (you will see an icon in your toolbar) you need to stop it before you begin debugging to force it be respawned so that it can pick up the Visual Studio environment variables. It's important to note that you need to stop the ASP Development Server every time you check or uncheck the "Oracle Application Debugging" option. You don't need to stop it every single time you run your app though. However, it's important to emphasize that you must stop the ASP Development Server when you are all done with PL/SQL debugging and have unchecked the "Oracle Application Debugging" - if you don't, your code will get connect errors.



3) Begin debugging!

Setup Instructions - Modifying the ASP.NET code

Setup Steps for situations where you are not using the ASP.NET Development Server and it is ok to add some code and rebuild your ASP.NET app:

1) Perform the generic setup steps 1 through 6 from my earlier blog entry and test via Oracle Explorer ("Step Into") that at least Direct Debugging is working.

2) If "Oracle Application Debugging" is checked off under the Tools menu, be sure to uncheck it.

3) Add code to your ASP.NET app to set the environment variable at the process level. You need to do this at a point in the code before a connection is opened. In C# this looks something like:

Environment.SetEnvironmentVariable("ORA_DEBUG_JDWP", "host=mymachine;port=8888", EnvironmentVariableTarget.Process);
4) It's a good idea to add code after the Oracle connection is closed that removes this environment variable. Since here we are setting this environment variable at a process level, it should not interfere with any other Oracle client apps running on your system. However it is important to note that if you do set this environment variable at something other than a process level (eg system level), you must be sure to unset it. All other Oracle apps on your system that see this environment variable will attempt to begin debugging everytime they connect and will get errors.

Environment.SetEnvironmentVariable("ORA_DEBUG_JDWP", "", EnvironmentVariableTarget.Process);
5) Set a breakpoint at some point BEFORE the first Oracle connection is made.

6) Start debugging your ASP.NET application and run to this breakpoint.

7) When the breakpoint hits, choose "Start Oracle External Application Debugger" from the "Tools" menu of Oracle Explorer. Provide it the same port number you gave in the environment variable above (in my case it was "8888"). Obviously your firewall cannot be blocking this port.



8) Set a breakpoint in the PL/SQL (double click on the procedure or function in Oracle Explorer to bring up the editor).

9) Set a breakpoint in your ASP.NET code AFTER the call to the stored procedure or function in ASP.NET to halt once control returns to ASP.NET

10) Begin debugging!

Setup Instructions - No Modification of ASP.NET code

In some cases, you might not wish to modify and rebuild the ASP.NET. So you can't set the ORA_DEBUG_JDWP environment variable in code like we did above. In those cases, you can set a systemwide environment variable and bounce IIS, but this is a bit more tedious:

In the steps above, replace steps 3 and 4 with the following:

3) Set ORA_DEBUG_JDWP as a system environment variable. Go to Control Panel, System and choose the "Advanced" tab. Click on the "Environment Variables" button. Set the value in the section labeled "System Variables". Make sure when you are done with your debugging session to unset this system wide environment variable because as mentioned earlier, it will interfere with other Oracle client applications on your system!



4) Stop and start the IIS Service from the Services Console (Control Panel->Administrative Tools) so that it picks up this environment variable.



....Then proceed with the rest of the steps in the list!

If you have additional questions about PL/SQL debugging, feel free to post to the Oracle Dev Tools forum!

Happy coding!