Debugging Oracle PL/SQL from Visual Studio

Purpose

This tutorial describes how you can debug Oracle PL/SQL from Visual Studio .

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Adding a Connection
 Granting Privileges for Debugging
 Creating the PL/SQL Package and Package Body
 Creating a Project in Visual Studio
 Configuring the Debugging Environment
 Setting Breakpoints
 Debugging PL/SQL
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

The integrated PL/SQL debugger in Oracle Developer Tools (ODT) allows you to remain inside of Visual Studio for end-to-end debugging of your .NET and Oracle solution. You can debug PL/SQL code such as procedures and functions (both stand-alone and packaged), object methods, and triggers from within the Visual Studio environment in the same way as you would debug your C# or VB.NET code. With the ODT integrated PL/SQL debugger, you can use traditional debugging features 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. By right clicking on a procedure or function in Server Explorer, you can step into a PL/SQL procedure or function, or run to a breakpoint you have set. You can, of course, pass arguments to and receive return values from the PL/SQL code.
External Application Debugging If you need to debug the PL/SQL code that is called by an application that is running outside of Visual Studio  (for example an application that is running 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 client libraries 9.2 or later) located on any platform without requiring you to modify the application. It is most often used for debugging ASP.NET applications in the middle tier.
Multi-tier Application Debugging The most powerful option during the development of a .NET application is the Multi-Tier Application Debugging mode. This mode allows 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.

In this tutorial, you use the Multi-tier Application Debugging mode. This mode allows you, as a developer, to work with both .NET and PL/SQL code simultaneously during a debugging session.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Microsoft Visual Studio 2008

2.

Install Oracle Database 9.2 or later or Oracle Database XE

3.

Install Oracle 11g Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio version 11.1.0.6.20 or later from OTN

4.

Download and unzip the codesnippets.zip file into your working directory.

Back to Topic List

Creating an Oracle Connection

Before you begin creating your application, you open Visual Studio and create a connection. Perform the following steps:

1.

Open Visual Studio. .

 

2.

Select View > Server Explorer.

 

3.

Right-click Data Connections and select Add Connection....

 

4.

In Add Connection dialog box, first select the appropriate Data Source provider.

Note: If the Data source is already set to Oracle Database Server(Oracle ODP.NET), then move to next step or click Change to select the Data Source provider.

In the Change Data Source dialog box, select Oracle Database Server as the Data Source and Oracle Data Provider for .NET as the Data Provider. Click OK.

 

5.

From the Data source name drop down, select the <SID> of your Oracle Database instance. Enter hr for the User name and Password, click Save password and click Test connection.

 

5.

Test connection was successful. Click OK.

 

6.

Click OK.

 

7.

Your HR connection has been created. Similarly, you also need to create a SYS connection. Right-click Data Connections and select Add Connection...

 

8.

The Data source provider should be set to Oracle Database Server(Oracle ODP.NET). From the Data source name drop down, select the <SID> of your Oracle Database instance.

Enter SYS for the User name and enter your sys Password, click Save password, select SYSDBA for the Role and click Test connection.

 

9.

Test connection was successful. Click OK.

 

10.

Click OK.

 

11.

The SYS connection was created. Expand both hr.ORCL and SYS.ORCL to make the connection.

 

Back to Topic List

Granting Privileges for Debugging

Before you can use the debugger, you need to grant debug connect session and debug any procedure to the HR user. Perform the following steps:

1.

In the Server Explorer panel, right-click SYS.ORCL connection and select Query Window.

 

2.

Enter the following command and click Execute.

GRANT debug any procedure, debug connect session TO hr;

Note: If you are using Oracle Database Release 9.2, you should execute grant debug any procedure to hr;

 

3.

The statement was executed successfully.

 

Back to Topic List

Creating the PL/SQL Package and Package Body

In this topic, you create a PL/SQL Package and Package Body that determines whether each number in a PL/SQL array is a prime number and then create a new row in the JOBS table using a PL/SQL record. Perform the following steps:

1.

In the Server Explorer panel, right-click HR.ORCL and select Query Window.

 

2.

Copy and paste the code in the plsql_debug_package.txt file into the Query Window and click Execute.

                               
CREATE OR REPLACE PACKAGE "HR"."OBE" 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 "OBE";

 

3.

The PL/SQL Package was executed successfully.

 

4.

Copy and paste the code in the plsql_debug_packagebody.txt file into the Query Window and click Execute.

                               
                                 
CREATE OR REPLACE PACKAGE BODY "HR"."OBE" IS
                                  
-- procedure that processes the incoming associative arrays
-- calls the method IS_PRIME to determine if element is likely prime
PROCEDURE "DETERMINE_PRIMES" ("P_IN_VALUES" IN T_IN_VALUES,
"P_OUT_VALUES" OUT T_OUT_VALUES) IS
BEGIN
-- loop through each element in the incoming array
-- and set the value for the corresponding element
-- in the out array
for i in p_in_values.first..p_in_values.last
loop
p_out_values(i) := is_prime(p_in_values(i));
end loop;
END "DETERMINE_PRIMES";
-- private function to determine if a number is likely prime
FUNCTION "IS_PRIME" ("P_NUMBER" IN NUMBER) RETURN NUMBER IS
l_sqrt number := 0;
l_sqrt_ceil number := 0;
l_divisor number := 0;
l_divisor_squared number := 0;
begin
-- prime numbers must be >= 2
if p_number < 2 then
return IS_NOT_A_PRIME;
end if;
-- only integers can be prime
if p_number != ceil(p_number) then
return IS_NOT_A_PRIME;
end if;
-- 2 is the only even prime, so it is a special case
if p_number = 2 then
return IS_A_PRIME;
end if;
-- eliminate all other even numbers
if mod(p_number,2) = 0 then
return IS_NOT_A_PRIME;
end if;
-- if the sqrt of the number is an integer, the number is not prime
l_sqrt := sqrt(p_number);
l_sqrt_ceil := ceil(l_sqrt);
if l_sqrt = l_sqrt_ceil then
return IS_NOT_A_PRIME;
end if;
-- the number has passed the basic elimination tests and may be prime
-- loop through set of odd divisors to determine if number is prime
l_divisor := 3;
for i in 1..l_sqrt_ceil
loop
l_divisor_squared := l_divisor * l_divisor;
-- if l_divisor is a factor of p_number, then not a prime
if mod(p_number,l_divisor) = 0 and l_divisor_squared < p_number then
return IS_NOT_A_PRIME;
end if;
-- no factor found, therefore number is likely a prime
if l_divisor_squared > p_number then
return IS_A_PRIME;
end if;
l_divisor := l_divisor + 2;
end loop;
END "IS_PRIME";
-- 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) IS
BEGIN
-- use the package variable JOBS_REC to create new record
jobs_rec.job_id := p_job_id;
jobs_rec.job_title := p_job_title;
jobs_rec.min_salary := p_min_salary;
jobs_rec.max_salary := p_max_salary;
-- insert the job record into the table
insert into jobs (job_id, job_title, min_salary, max_salary)
values (jobs_rec.job_id, jobs_rec.job_title,
jobs_rec.min_salary, jobs_rec.max_salary);
END "ADD_NEW_JOB";
END "OBE";

 

5.

The PL/SQL Package Body was executed successfully.

 

6.

In the Server Explorer for the HR.ORCL Connection, expand Packages > OBE to see the list of objects that were created.

 

7.

Right-click the OBE Package and select Compile Debug. This action enables debugging of the package.

 

8.

The package compiled successfully. Close the Output window. And then click the Start Page tab.

 

Back to Topic List

Creating a Project in Visual Studio

Now you can create a new project. Perform the following steps:

1.

From the File menu, select New Project.

 

2.

Accept the default Project Type Visual C# , select the Template Console Application , enter plsqldebugobe1 for the Name and enter the directory where you want the files stored in the location field (Note: if the directory does not exist, it is created). Then click OK.

 

3.

The project has been created. Now you can add a reference.

 

4.

Select Project > Add Reference...

 

5.

Scroll down the list of References and select Oracle.DataAccess and click OK.

 

6.

Copy and paste the code in the plsql_debug_csharpcode.txt file into the Program.cs window in Visual Studio. Then right-click on the Program.cs tab and select Save Program.cs.

                               
                                 
                                   
using System;
                                    
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace plsqldebugobe1
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Program
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
// constants used to represent values returned
// from the pl/sql procedure call
const int IS_NOT_A_PRIME = 0;
const int IS_A_PRIME = 1;
// display progress message
Console.WriteLine("Testing array for prime numbers...\n");
// connection string: adjust for your environment
string constr = "User Id=hr; Password=hr; Data Source=ORCL; enlist=false; pooling=false";
// create and open connection object
OracleConnection con = new OracleConnection(constr);
con.Open();
// create command object for the function call
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "OBE.determine_primes";
// set the proper command type
cmd.CommandType = CommandType.StoredProcedure;
// parameter object for the input array
OracleParameter p_in_values = new OracleParameter();
p_in_values.OracleDbType = OracleDbType.Decimal;
p_in_values.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_in_values.Value = new decimal[10]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10};
p_in_values.Size = 10;
p_in_values.Direction = ParameterDirection.Input;
// parameter object for the output array
OracleParameter p_out_values = new OracleParameter();
p_out_values.OracleDbType = OracleDbType.Decimal;
p_out_values.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_out_values.Value = null;
p_out_values.Size = 10;
p_out_values.Direction = ParameterDirection.Output;
// add parameters to the collection
// they must be added in the proper
// order when using bind by position (the default)
cmd.Parameters.Add(p_in_values);
cmd.Parameters.Add(p_out_values);
// execute the pl/sql procedure to populate output array
cmd.ExecuteNonQuery();
// display results to console window
for (int i = 0; i < p_in_values.Size; i++)
{
foreach (OracleParameter p in cmd.Parameters)
{
// the input array is treated as System.Decimal[]
// within the .NET code
if (p.Value is System.Decimal[])
{
Console.Write("The Number {0} ", ((p.Value as System.Decimal[])[i]).ToString());
}
// the output array is treated as OracleDecimal[]
// within the .NET code
if (p.Value is OracleDecimal[])
{
if (((p.Value as OracleDecimal[])[i]).ToInt32() == IS_NOT_A_PRIME)
{
Console.WriteLine("is not a prime number!");
}
else if (((p.Value as OracleDecimal[])[i]).ToInt32() == IS_A_PRIME)
{
Console.WriteLine("is a prime number!");
}
}
}
}
// display a separator line
Console.WriteLine();
// display progress message
Console.WriteLine("Using PL/SQL record...\n");
// remove parameters from command collection
// and set new command text
cmd.Parameters.Clear();
cmd.CommandText = "
obe .add_new_job";
// parameter object for the job_id
OracleParameter p_job_id = new OracleParameter();
p_job_id.Value = "IT_DBA";
// parameter object for the job_title
OracleParameter p_job_title = new OracleParameter();
p_job_title.Value = "Database Administrator";
// parameter object for the min_salary
OracleParameter p_min_salary = new OracleParameter();
p_min_salary.OracleDbType = OracleDbType.Decimal;
p_min_salary.Value = 10000;
// parameter object for the max_salary
OracleParameter p_max_salary = new OracleParameter();
p_max_salary.OracleDbType = OracleDbType.Decimal;
p_max_salary.Value = 15000;
// add parameters to collection
cmd.Parameters.Add(p_job_id);
cmd.Parameters.Add(p_job_title);
cmd.Parameters.Add(p_min_salary);
cmd.Parameters.Add(p_max_salary);
// execute the pl/sql procedure to add new job
cmd.ExecuteNonQuery();
// display simple message to indicate procedure completed
Console.WriteLine("New job successfully created!");
// display a separator line
Console.WriteLine();
// Simple prompt to prevent the console from closing
// when running from the IDE
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
// clean up objects
p_max_salary.Dispose();
p_min_salary.Dispose();
p_job_title.Dispose();
p_job_id.Dispose();
p_out_values.Dispose();
p_in_values.Dispose();
cmd.Dispose();
con.Dispose();
}
}
}

 

Back to Topic List

Configuring the Debugging Environment

There are a few properties and options that need to be configured before you can use the debugging environment. Perform the following steps:

1.

Select Project > plsqldebugobe1 Properties...

 

2.

Select Debug.

 

3.

Deselect Enable the Visual Studio hosting process and right-click the plsqldebugobe1 tab and select Save Selected Items.

 

4.

Select Tools > Options....

 

5.

Click the Show all settings check box.

 

6.

Scroll down and expand Oracle Developer Tools. Select PL/SQL Debugging. In the Available database connections, click the checkbox in front of the HR.ORCL connection.

Notice that you see the TCP/IP port range. During PL/SQL debugging, the Oracle Database connects 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.

Click OK.

 

7.

Select Tools > Oracle Application Debugging.

If you select Tools > Oracle Application Debugging again, you should see a checkmark in front of the menu item.

 

Back to Topic List

Setting Breakpoints

In this topic, you set some breakpoints in both your C# and PL/SQL code to stop the debugger during execution. Perform the following steps:

1.

Click the Program.cs tab and locate the cmd.ExecuteNonQuery() statement.

 

2.

In the Program.cs code,at the cmd.Parameters.Add(p_out_values) statement, you want to create a breakpoint. Click anywhere on the line, right-click and select Breakpoint, then select Insert Breakpoint. Alternatively, you can click on the grey leftmost edge of the code window at the location where you would like to set the breakpoint.

 

3.

The breakpoint indicator is shown.

 

4.

Create another breakpoint after the package is executed.

 

5.

In the Server Explorer, double-click DETERMINE_PRIMES to open the code.

 

6.

Create a breakpoint at the first statement after the BEGIN statement in the DETERMINE_PRIMES procedure.

 

7.

From the list of procedures, select ADD_NEW_JOB.

 

8.

Create a breakpoint at the first statement after the BEGIN statement.

 

9.

Click the Program.cs tab.

 

Back to Topic List

Debugging PL/SQL

In this topic, you execute the program using the debugger. Perform the following steps:

1.

Now you are ready to debug your program. Select Debug > Start Debugging...

 

2.

The debugger stops at the first breakpoint. To see the variables and their values, click the Local tab at the bottom of the window. Have a look at the code leading up to this breakpoint to understand how an array bind parameter is set up.

 

3.

To move to the next line, click the Step Into icon.

 

4.

You are at the statement that executes the PL/SQL Procedure. Click the Step Into icon.

 

5.

The next breakpoint is reached in the DETERMINE_PRIMES procedure. Notice that the P_IN_VALUES is an array of length 10 so the loop will be performed 10 times. Click the Continue icon a couple of times to see the values in the Local window change.

 

6.

Click on the + icon next to the P_IN_VALUES variable name in the Locals window to view the contents of the input array. This is the array of values that was passed into this stored procedure from the C# application. Continue stepping through the code for a while. You can also expand the P_OUT_VALUES array to watch as it gets filled with values that are eventually returned by this stored procedure.

 

7.

Click the Call Stack tab in the lower right window. By examining the call stack, you can determine the code path to the current point in the execution of your program.

 

8.

Disable this breakpoint so the program continues to the next breakpoint. Right-click the breakpoint and select Disable Breakpoint. You can also click directly on the red circle breakpoint icon to delete the breakpoint.

 

9.

Click the Continue icon so that the program executes until the next Breakpoint.

 

10.

The code performs the logic to produce what gets displayed to the user. Click Step Into a few more times.

 

11.

Click the Continue icon again to go to the next Breakpoint.

 

12.

The ADD_NEW_JOB procedure breakpoint is reached.

 

13.

If you want to view the JOBS_REC PL/SQL record global variable, you need to create a watch. Select JOBS_REC and right-click, then select Add Watch. Then select the Watch tab at the bottom of the window.

 

14.

The Watch window is a built-in window in Visual Studio that allows you to examine specific program variables. Expand the JOBS_REC Watch.

 

15.

Click Step Into 4 times to see the Watch get populated. Then click Continue.

 

16.

When the program finishes executing the results are shown.

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Add a connection to the database and grant privileges for debugging
 Create a package and package body
 Create a project and configure the debug environment
 Debug PL/SQL

Back to Topic List

 Move your mouse over this icon to hide all screenshots.

 

Left Curve
Popular Downloads
Right Curve
Untitled Document