Install Microsoft Visual Studio 2008

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.1.0.6.20 or later from OTN

Download and unzip the codesnippets.zip file 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 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";

The PL/SQL Package was executed successfully.

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";

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.

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.

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 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();
    }
  }
}

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.

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.

Select Tools > Oracle Application Debugging.

If you select Tools > Oracle Application Debugging 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.