Debugging PL/SQL Stored Procedures from Visual Studio
From TroubleshootingWiki
| Official Page |
| Project Documentation |
| Download |
|
ODT is tightly integrated with Visual Studio even to the level of debugging PL/SQL stored procedures. Before using the PL/SQL debugging feature, we need to configure the database and Visual Studio environment to enable PL/SQL debugging. Let us start configuring the database first.
We need to provide a few privileges for user Scott, to allow him to debug PL/SQL stored procedures. Once he is provided with the privileges, we will create a sample stored procedure and develop a small Windows (desktop) application, and finally debug the application together with a PL/SQL stored procedure.
Log in with DBA privileges (or log in as SYSTEM user) and execute the following two commands:
SQL>grant debug any procedure to scott; SQL>grant debug connect session to scott;
Open your Visual Studio IDE and create a new Windows Application project. In the Project properties of the application, make sure that Enable the Visual Studio hosting process is checked off (in the Debug tab) as follows. This is required as the debugging process crosses beyond the Visual Studio Debugger level.
Go to the Tools menu and switch on Oracle Application Debugging as follows:
Go to the Tools menu again and within the Options, switch on PL/SQL debugging (of ODT) for all the necessary connections (you may have to connect to the database using Oracle Explorer prior to doing this) as follows:
Develop a stored procedure in Oracle database (in SCOTT user) as follows:
CREATE OR REPLACE PROCEDURE p_emp_details(p_empno emp.empno%TYPE, p_ename OUT emp.ename%TYPE, p_AnnSal OUT NUMBER, p_deptno OUT emp.deptno%TYPE)AS v_Sal emp.sal%TYPE; v_AnnSal NUMBER(11,2); BEGIN SELECT ename, sal, deptno INTO p_ename, v_Sal, p_deptno FROM emp WHERE empno = p_empno; v_AnnSal := v_Sal * 12; p_AnnSal := v_AnnSal; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20001, 'Employee not found'); WHEN TOO_MANY_ROWS THEN /* this would not happen generally */ RAISE_APPLICATION_ERROR(-20002, 'More employees exist with the same number'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20003, SQLERRM); END;
Drag a button from the toolbox on to the Windows form, add reference to Oracle.DataAccess.dll, and copy the following code, which executes the above stored procedure:
Imports Oracle.DataAccess.Client
Public Class Form1
Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Button1.Click
Dim cn As New OracleConnection("data source=xe; _
user id=scott;password=tiger")
Dim cmd As New OracleCommand("p_emp_details", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("p_empno", OracleDbType.Double)
cmd.Parameters.Add("p_ename", OracleDbType.Varchar2,
20, Nothing, ParameterDirection.Output)
cmd.Parameters.Add("p_AnnSal", OracleDbType.Double,
ParameterDirection.Output)
cmd.Parameters.Add("p_deptno", OracleDbType.Int16,
ParameterDirection.Output)
cmd.Parameters("p_empno").Value = 7369
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cn.Close()
MsgBox(cmd.Parameters("p_ename").Value.ToString & ","
& Convert.ToString(cmd.Parameters("p_AnnSal").Value))
End Sub
End Class
This code starts with creating an OracleConnection and adds an OracleCommand, which is linked with the stored procedure p_emp_details. As the stored procedure accepts four parameters, all of them are added using OracleCommand. Once the stored procedure gets executed using the ExecuteQuery method, the output parameters (p_ename, p_AnnSal, and p_deptno) get filled with values, which are finally displayed using MsgBox.
Using the Oracle Explorer, right-click on P_EMP_DETAILS and click on Compile Debug as follows:
That will put the stored procedure into debug mode as follows:
Now, place some break points in your .NET code as follows:
Similarly, double-click on the stored procedure (in Oracle Explorer) and place break points as shown in the following screenshot:
Finally hit F5 to debug the application. It runs through each of the break points available in the .NET code as follows:
On hitting F5 again, it starts to debug the stored procedure as follows:
You can also observe the local variables and their values during debugging. Hitting F5 further, you should be able to observe that the values get assigned to variables as follows:
Finally, the control comes back to the Visual Studio environment (after debugging the PL/SQL stored procedure) and waits at the final break point as follows:
[edit] Source
The source of this content is Chapter 9: Introduction to Oracle Developer Tools for Visual Studio 2005 of ODP.NET Developer’s Guide by Jagadish Chatarji Pulakhandam, Sunitha Paruchuri(Packt Publishing, 2007).
