Debugging PL/SQL from .NETBy Mark A. Williams
Debug PL/SQL in Microsoft Visual Studio with Oracle Developer Tools.
Debugging PL/SQL code in Microsoft Visual Studio is as easy as debugging native .NET code, thanks to the PL/SQL debugger in Oracle Developer Tools for Visual Studio .NET (ODT). This column demonstrates how to set up PL/SQL debugging in ODT and how to create and step through PL/SQL code from within your .NET solution.
To use PL/SQL debugging in ODT, you must have the following:
To step through the PL/SQL code that accompanies this column, you must also have access to the HR sample schema included with Oracle Database.
The Integrated PL/SQL Debugger
The new, integrated PL/SQL debugger in ODT 10.2.0.2 allows you to remain inside Visual Studio for end-to-end debugging of your .NET and Oracle solution. You can now debug PL/SQL code such as procedures and functions (both standalone and packaged), object methods, and triggers from within the Visual Studio environment in the same way you would debug your C# or VB code. With the ODT integrated PL/SQL debugger, you can do traditional debugging tasks such as setting break points, viewing and modifying variable values, and examining the call stack.
You use the PL/SQL debugger in one of three modes:
Direct database debugging. The direct database debugging mode allows you to debug PL/SQL code directly from the Visual Studio environment. When you use the direct database debugging mode, you do not need a Visual Studio solution or .NET code—you work directly with the PL/SQL code in the database.
External application debugging. If you need to debug the PL/SQL code that is called by a compiled binary or an application that is on a non-Windows platform, use the external application debugging mode. This mode allows you to debug PL/SQL programs that are called by any application (built with Oracle Database client libraries 9.2 or later) located on any platform without requiring you to modify the application.
Multitier application debugging. The most powerful option during the development of a .NET application is the multitier application debugging mode, which enables you to seamlessly debug both .NET and PL/SQL code from within your Visual Studio solution. You can step directly from your .NET code into the PL/SQL code and back out again.
This article shows how to use the multitier application debugging mode. Because this mode allows you to work with both .NET and PL/SQL code simultaneously during a debugging session, I've found it to be the most commonly used of the three modes.
Create a New Connection
The first step to PL/SQL debugging with ODT is to create a new connection in Oracle Explorer in Visual Studio. If Oracle Explorer is not visible, select View -> Oracle Explorer from the main menu. The top-level node of Oracle Explorer is Data Connections . To create a new connection in the Data Connections node, right-click Data Connections and select Add Connection... . In Data source name , select or type in the name of the datasource you want to use. The datasource is also commonly known as the tns-alias or tnsnames file entry. Click Use a specific user name and password . In the User name field, enter HR. In the Password field, enter the password for the HR user in your database. Ensure that the Role list has the value Default . Change the proposed Connection name to HR.ORAMAG . Click Test connection to make sure the connection details are all correctly specified. Click OK to complete the connection setup.
Repeat the steps above to establish an additional connection for the SYSTEM user with the SYSDBA role if you have the privileges to do so. Name the connection SYSTEM.ORAMAG. You will need this SYSTEM connection to grant the appropriate debugging privileges to HR.
Grant the Required Privileges
If you are using Oracle Database 10g (10.1 or later), you or your DBA must grant two necessary privileges to the HR user: DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE. If you are using Oracle9i Database (9.2), only the DEBUG ANY PROCEDURE privilege is required. (As an alternative to the DEBUG ANY PROCEDURE privilege, you can grant both DEBUG and EXECUTE permissions on the particular PL/SQL program being debugged). With these privileges, the HR user can debug PL/SQL code from within the Visual Studio environment.
To grant these privileges, use the DBA connection and the query window provided by ODT. To open the query window, right-click the SYSTEM.ORAMAG node under Data Connections in Oracle Explorer and select Query Window . To grant the first privilege, type GRANT DEBUG CONNECT SESSION TO HR in the query editor and click Execute . To grant the second privilege, delete the existing text in the query editor, type GRANT DEBUG ANY PROCEDURE TO HR , and click Execute .
Create the PL/SQL Package and Body
With a connection to the database created and the necessary privileges granted, you can now create the PL/SQL example code used in this article to demonstrate the features of the PL/SQL debugger. You create a PL/SQL package and a PL/SQL package body. The PL/SQL package describes and exposes (or publishes) the functionality in the package body, which is where the functionality described in the package is implemented. The PL/SQL code example in Listings 1 and 2 determines whether each number in a PL/SQL array is a prime number and creates a new row in the JOBS table using a PL/SQL record.
To create the PL/SQL code, use the query window within ODT. To display the query window, right-click the HR.ORAMAG connection node in Oracle Explorer and select Query Window from the context menu. Enter the text of Listing 1 in the Query Editor field, and click Execute to create the package. Next, replace the text in the Query Editor field with the text of Listing 2 and click Execute to create the package body. The Listing 1 and Listing 2 code is available in the Database.sql script included with this column's code download.
Code Listing 1: The HR.ORAMAG PL/SQL package code
CREATE OR REPLACE PACKAGE "HR"."ORAMAG" IS -- types for associative arrays that client will pass as arguments TYPE "T_IN_VALUES" IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE "T_OUT_VALUES" IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; -- procedure that accepts two associative arrays -- determines if an element is likely prime and -- sets value in output array PROCEDURE "DETERMINE_PRIMES" ("P_IN_VALUES" IN T_IN_VALUES, "P_OUT_VALUES" OUT T_OUT_VALUES); -- function that determines if a number is likely prime FUNCTION "IS_PRIME" ("P_NUMBER" IN NUMBER) RETURN NUMBER; -- constants used to return values from function IS_NOT_A_PRIME CONSTANT NUMBER DEFAULT 0; IS_A_PRIME CONSTANT NUMBER DEFAULT 1; -- pl/sql record type for the jobs table "JOBS_REC" jobs%rowtype; -- pl/sql procedure to add new job to jobs table PROCEDURE "ADD_NEW_JOB" ("P_JOB_ID" IN JOBS.JOB_ID%TYPE, "P_JOB_TITLE" IN JOBS.JOB_TITLE%TYPE, "P_MIN_SALARY" IN JOBS.MIN_SALARY%TYPE, "P_MAX_SALARY" IN JOBS.MAX_SALARY%TYPE); END "ORAMAG";
You must compile the PL/SQL code with debug information to enable debugging. To do this, go to Oracle Explorer and expand the Packages node. Find the ORAMAG package; right-click it; and from the menu, select Compile Debug . The icon next to the ORAMAG package and the individual procedures and functions underneath it will change to reflect that it has been compiled with debug information.
Create the .NET Project
To create a new .NET project to test and debug the PL/SQL code, select File -> New -> Project from the Visual Studio main menu. Select Visual C# Projects from the Project Types list. Select Console Application from the Templates list, and give the new project a name such as SepOct2006 in the Name field. You must add a reference to the ODP.NET assembly to your project to access the PL/SQL code. To add the reference, select Project -> Add Reference from the Visual Studio main menu. Select Oracle.DataAccess.dll from the Component Name list. Now that the reference to the ODP.NET assembly has been added, replace the template C# code generated by Visual Studio with the code in Listing 3.
When you create a new project, the Enable the Visual Studio hosting process debug option is enabled by default. Oracle recommends that you disable it, by selecting Project -> Properties... from the Visual Studio main menu, clicking the Debug tab, and unchecking Enable the Visual Studio hosting process in the Enable Debuggers list. Alternatively, you will need to start and then terminate the debugger once before the PL/SQL debugger can work properly.
Configuring the Debugger Options
To debug the PL/SQL code seamlessly from the Visual Studio environment, you need to configure ODT to locate the PL/SQL code to be debugged: Select Tools -> Options from the Visual Studio main menu, select Oracle Developer Tools from the Options list, and then click the check box next to HR.ORAMAG in the Available Database Connections list. The PL/SQL debugger may identify the code to be debugged by looking for any checked connections, so if you have other connections defined, ensure that they are not checked.
In the same location, you will see a TCP/IP port range. During PL/SQL debugging, Oracle Database will connect to Visual Studio via TCP/IP on a random port within this range. Make sure this range represents open ports on your machine and that they are not blocked by a firewall.
The last step in configuring ODT to debug PL/SQL alongside your .NET code is to enable application debugging. To do this, select Tools -> Oracle Application Debugging from the Visual Studio main menu and ensure that there is a check mark next to Oracle Application Debugging in the menu, indicating that it is enabled.
Debugging the PL/SQL Code
Now that you have created the PL/SQL code, granted the appropriate privileges to the HR user, and created a C# project, you can debug the code from within the Visual Studio environment.
Setting break points. You must set break points in both your C# and PL/SQL code to stop the debugger during execution. Set a break point in your C# code after the ExecuteNonQuery call, which executes the DETERMINE_PRIMES PL/SQL procedure. In addition, set a break point in your PL/SQL code after the BEGIN keyword in the DETERMINE_PRIMES procedure. If you don't set a break point in your PL/SQL code, the debugger will not stop inside the PL/SQL code body. If you don't set a break point in your C# code, the debugger will not stop inside the C# code when you return from the PL/SQL code. To compile and run the solution in debug mode, select Debug -> Start Debugging from the Visual Studio menu. The program will begin running, and the ODT debugger will stop execution on your break point in the PL/SQL code. Press the F11 key to step through the PL/SQL code line by line.
Viewing and modifying variable values. While debugging the example, you can watch the C# application set up and call the DETERMINE_PRIMES PL/SQL procedure, which accepts an array in its first parameter. When working with the PL/SQL debugger in Visual Studio, you can see
In addition to examining types such as arrays while debugging, you can examine other complex types, such as each element in a PL/SQL record type or a user-defined type.
To continue executing the program, press the F5 key until the Visual Studio debugger halts execution on the break point you previously set in the C# code. Set a break point in the ADD_NEW_JOB PL/SQL procedure. You can then press F5 once more to continue execution until you reach the break point in the ADD_NEW_JOB procedure. To step through this procedure line by line, press F11.
To view or drill down into a global PL/SQL package variable such as JOBS_REC, you must use the Watch window, a built-in window in Visual Studio that allows you to examine specific program variables. To add JOBS_REC to the Watch window, right-click JOBS_REC in the code and select Add Watch from the context menu.
View the PL/SQL call stack. Another important PL/SQL debugging feature is the ability to view the PL/SQL call stack. By examining the call stack, you can determine the code path to the current point in the execution of your program.
To view the call stack, click Visual Studio's Call Stack tab, which is available by default in debug mode.
You can double-click on any level of the stack to go to the corresponding line of code.
With the example code provided with this column, you can take your own tour of the PL/SQL debugger. It offers the same debugging features you are already familiar with from your experience in debugging C# or VB code, and the ability to seamlessly debug both .NET and PL/SQL code from a common environment is a welcome addition to the tools available for the .NET and Oracle programmer.
Mark A. Williams (email@example.com) is an Oracle ACE, an Oracle Certified Professional DBA, and the author of Pro .NET Oracle Programming (Apress, 2004).