Debugging Oracle PL/SQL from Visual Studio

Install Microsoft Visual Studio 2010

Install Oracle Database 9.2 or later or Oracle Database XE

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

Extract these files into your working directory.

Open Visual Studio..

Select View > Server Explorer.

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

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.

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.

Test connection was successful. Click OK.

Click OK.

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

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

Test connection was successful. Click OK.

Click OK.

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

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

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;

The statement was executed successfully.

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

Copy the code in the PLSQL_Debug_Package.txt file from the files downloaded into your working directory and paste 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";

The PL/SQL Package was executed successfully.

Copy the code in the PLSQL_Debug_Packagebody.txt file from the files downloaded into your working directory and paste 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";

The PL/SQL Package Body was executed successfully.

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

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

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

From the File menu, select New Project.

Choose Project Type Visual C# : Windows, 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.

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

Select Project > Add Reference...

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

Copy the code in the PLSQL_Debug_csharpcode.txt file from the files downloaded into your working directory and paste 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();
}
}
}

Select Project > plsqldebugobe1 Properties...

Select Debug.

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

Select Tools > Options....

Click the Show all settings check box. Note: You may not see this checkbox which means that it was already set.

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 an IP address dropdown box and a TCP/IP port range. During PL/SQL debugging, the Oracle Database connects to Visual Studio via TCP/IP on an IP address and on a random port within this range. If your machine has multiple IP addresses make sure to choose one that the Oracle database can connect to. Make sure the port range specified represents open ports on your machine and that they are not blocked by a firewall.

Select Tools > Oracle Application Debugging.

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

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

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.

The breakpoint indicator is shown.

Create another breakpoint after the package is executed.

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

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

From the list of procedures, select ADD_NEW_JOB.

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

Click the Program.cs tab.

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

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.

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

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

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.

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.

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.

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.

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

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

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

The ADD_NEW_JOB procedure breakpoint is reached.

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.

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

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

When the program finishes executing the results are shown.