email article printer friendly

Troubleshoot PL/SQL
by Andy Clark

February 2003 Issue

The DevPartnerDB Debugger for Oracle Visual Studio Edition simplifies debugging Oracle stored procedures from within .NET. It integrates fully into VS.NET and—once you do a little setup—operates identically to the VS.NET debugger.

You gain access to the PL/SQL Debugger project once you install DevPartnerDB. You use this new project type to add Oracle stored-procedure debugging to your applications. When you activate the PL/SQL Debugger project, you're dropped into DevPartnerDB whenever your code uses an Oracle function or procedure. DevPartnerDB allows you to set breakpoints, examine and modify PL/SQL variables, set watches, and view the call stack. It performs all the functions the VS.NET debugger supports, including menu functions and special keys. DevPartnerDB also color codes SQL keywords and letters and displays a variable's value when you hover over it with the cursor.

You can step through your PL/SQL to examine both flow of execution and variable values until you find all problems—a big improvement over running the function and examining its results until the problem becomes apparent. DevPartnerDB even steps into nested procedures, including Oracle built-in functions such as NVL. These capabilities should be everything you need to troubleshoot your PL/SQL. Compuware has also assured me DevPartnerDB works with the new Oracle provider objects, though I did all my testing with the standard OleDb objects.

Tame PL/SQL.

I was disappointed by a few aspects. The documentation could be improved; I'd like to see Compuware include a beginner's tutorial showing how to set up both the .NET code and the PL/SQL. You must compile your stored procedures for debugging before they'll work. You also have to activate stored procedures with OleDbCommand objects using the adCmdStoredProc command type. The code window allows you to edit your PL/SQL, but there's no mechanism for saving your edits back to the database, so the code window should probably be read-only. Finally, I wasn't able to get the debugger to activate for a function I used in a Select statement. Compuware support was helpful getting me through all these limitations, but the company needs to address them within the product's documentation. However, none of them is a serious issue. They're typical of problems in early debugger releases, and you can work around them easily.

DevPartnerDB is a strong tool for integrating PL/SQL and VS.NET development. It's a full-featured debugger with a few limitations, strong support, and documentation that needs work. This debugger can save you many hours of frustration. If you use PL/SQL and VS.NET extensively, DevPartnerDB merits your serious consideration.

About the Author
Andy Clark is a consultant in the Richmond, Va., area. He's an MCSD and JCP with extensive Oracle and SQL Server experience. Reach him at .