Using .NET Stored Procedures in Oracle
by Mark A. Williams

A step-by-step guide to developing, deploying, and debugging your .NET Stored Procedures in Oracle Database 10g Release 2

Downloads for this article Sample code Oracle Developer Tools for Visual Studio .NET, version 10.2 or later Oracle Database 10g, version 10.2 or later

Published November 2005

If you are a .NET developer, one of the most exciting features of Oracle Database 10g Release 2 for Windows is the ability to implement stored procedures using the .NET language of your choice, via Oracle Database Extensions for NET.

In this installment of Mastering .NET Application Development with Oracle, you will learn a step-by-step approach for taking advantage of .NET stored procedures in your applications. You will learn how .NET stored procedures are supported, how to install and configure the Oracle Database to enable support for .NET stored procedures, how to develop and deploy .NET stored procedures, and, finally, how to debug .NET stored procedures.

Supporting Architecture

PL/SQL stored procedures and functions run in the same process as the Oracle Database and are stored inside of Oracle. A .NET stored procedure, on the other hand, runs in an external process and the .NET code is compiled into a ".NET assembly," which is a dynamic link library (DLL) file stored in the file system (usually on the same machine as the database). The .NET assembly is loaded into and executed inside of a "CLR host" external process named extproc.exe, which is spawned by the Windows service named <OracleHomeName>ClrAgnt. When a .NET stored procedure call is made, Oracle communicates with this external process, passing in the arguments and retrieving the results. This communication is handled by the Oracle multithreaded agent architecture. To the end user, a .NET stored procedure call appears to be no different than any other type of stored procedure call. In fact, a .NET stored procedure can be called from any environment where you could call a PL/SQL or Java stored procedure.

Installation and configuration:

If you are using Oracle Database 10g Express Edition, .NET stored procedures are automatically installed and configured—no additional configuration is required. In the Standard and Enterprise Editions, however, .NET stored procedures are not installed and configured by default. Here are basic instructions to get everything working for those installs:

1. Download Oracle Database 10.2 or later for the Windows platform. (Note: .NET stored procedures are not supported on any other platform!)
2. Run the installer (setup.exe).
3. Choose Advanced Installation and then Custom.
4. Along with your other installation choices, make sure to tick off Oracle Database Extensions for .NET.
5. When the installation is complete, run the Database Configuration Assistant (from the Start Menu, Oracle, Configuration and Migration Tools).
6. In the assistant, choose to configure the Oracle Database Extensions for .NET database option.
7. When complete, make sure the <OracleHome>ClrAgent service has started.
8. Download the Oracle Developer Tools for Visual Studio .NET version 10.2 or later and install. This version is required for you to be able to deploy your .NET stored procedures.

Develop and Deploy a .NET Stored Procedure

You will develop and deploy a simple, yet fully functional, .NET stored procedure to retrieve a country name based on a country code. You will use the countries table in the HR sample schema that is part of Oracle Database 10g Release 2. The countries table has the following structure:

SQL> desc countries
 Name           Null?    Type
 -------------- -------- ------------
 COUNTRY_ID     NOT NULL CHAR(2)
 COUNTRY_NAME            VARCHAR2(40)
 REGION_ID               NUMBER

Create a new Oracle Project called "MyStoredProcedure" and you will see that the project wizard has added a reference to the Oracle Data Provider for .NET assembly and added the ODP.NET namespaces to the class file. The Visual Studio .NET 2003 development environment should resemble the following:

You will replace the StoredProcedure1 procedure generated by the project wizard with your code to get a country name from the countries table. Your new procedure will accept a country ID as an integer parameter and return the country name as a string value. A key point to notice as you develop your procedure is that the code in a .NET stored procedure is virtually identical to the code you use in a stand-alone program. In fact, the only difference you will see is the connection string used to establish the connection to the database. Here is the complete code:

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace MyStoredProcedure
{
  /// <summary>
  /// Summary description for Class1.
  /// </summary>
  public class Class1
  {
    public static string GetCountryName(string CountryID)
    {
      // used to return the country name
      string CountryName = "";

      // Get a connection to the db
      // context connection is used in a stored procedure
      OracleConnection con = new OracleConnection();
      con.ConnectionString = "context connection=true";
      con.Open();

      // Create command and parameter objects
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "select country_name from countries where country_id = :1";
      cmd.Parameters.Add(":1", OracleDbType.Varchar2, CountryID, ParameterDirection.Input);
      
      // get a data reader
      OracleDataReader rdr = cmd.ExecuteReader();

      // get the country name from the data reader
      if(rdr.Read())
      {
        CountryName = rdr.GetString(0);
      }
      
      // clean up objects
      rdr.Close();
      cmd.Dispose();

      // Return the country name
      return CountryName;
    }
  }
}

Before you deploy your stored procedure, you must first build the project. Since you will debug your procedure after deploying it, make sure you perform a debug build of the project. After building the project, you deploy it to the database using a deployment wizard. At this time, you must use a SYSDBA connection to the database in order to deploy the stored procedure. If you do not have a SYSDBA connection available in the Data Connection node within the Oracle Developer Tools for Visual Studio .NET, the wizard will allow you to create one.

To deploy the stored procedure, select Build→ Deploy Solution from the Visual Studio .NET 2003 menu bar. This will start the deployment wizard:

After reviewing the information on the first step of the wizard, click on Next. You will then be prompted to select the database connection to use. If you do not have a database connection already defined, click on the New Connection button. Otherwise, choose the SYSDBA-enabled connection you would like to use:

After selecting the database connection to use, click on the Next button. This step in the wizard allows you to select the deployment option you wish to use. Because you have not yet deployed this procedure, accept the default option to deploy the assembly and create stored procedure wrappers in the database:

Click on the Next button to proceed. You specify the name of the DLL that will be used when the project has been deployed:

Accept the default values supplied by the wizard and click on Next to specify the directory to which the project will be deployed:

Simply click on Next to accept the default values. This will deploy the project to the %ORACLE_HOME%\bin\clr directory. The next step allows you to specify which methods to deploy, the schema in which the methods should be deployed, the name of the method in the database, the security level, and the type mappings:

To deploy the project correctly, select the GetCountryName method, select HR as the schema, accept the supplied database method name, and accept the supplied security level.

There are three security levels available:

• Safe: Access to database resources only. No access to resources such as the local file system or network connections
• External: Access to resources such as file systems and network connections
• Unsafe: No restrictions

The wizard has selected values based on the .NET types in your code. Accept these values as they are appropriate.

The final step in the deployment wizard presents a summary of the options selected and allows you to view a script of the actions to be performed. To perform the actual deployment, click on the Finish button. Once the deployment is complete you can verify the file has been created by examining the deployment directory:

C:\>dir c:\oracle\10.2\database\bin\clr
 Volume in drive C is Local Disk
 Volume Serial Number is 94FF-538C

 Directory of c:\oracle\10.2\database\bin\clr

09/10/2005  07:18 PM    <DIR>          .
09/10/2005  07:18 PM    <DIR>          ..
09/10/2005  07:18 PM            16,384 MyStoredProcedure.dll
               1 File(s)         16,384 bytes
               2 Dir(s)  26,689,114,112 bytes free

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Sep 10 19:21:47 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect hr
Enter password:
Connected.
SQL> select GetCountryName('FR') from dual;

GETCOUNTRYNAME('FR')
---------------------------------------------------------------------
France

1 row selected.

This will produce the Run Function dialog:

Enter FR and click on the OK button. This will produce the following result window in the Visual Studio .NET 2003 IDE:

Debugging a .NET Stored Procedure

In order to debug the deployed stored procedure from within the Visual Studio .NET 2003 IDE, you need to copy the Program Debug Database file to the directory where the DLL was deployed. Copy the MyStoredProcedure.pdb file to the %ORACLE_HOME%\bin\clr directory:

C:\>dir c:\oracle\10.2\database\bin\clr
 Volume in drive C is Local Disk
 Volume Serial Number is 94FF-538C

 Directory of c:\oracle\10.2\database\bin\clr

09/10/2005  07:32 PM    <DIR>          .
09/10/2005  07:32 PM    <DIR>          ..
09/10/2005  07:18 PM            16,384 MyStoredProcedure.dll
09/10/2005  06:44 PM            11,776 MyStoredProcedure.pdb
               2 File(s)         28,160 bytes
               2 Dir(s)  26,681,720,832 bytes free

Because the DLL file is loaded by the extproc.exe process, you must attach to that process in Visual Studio .NET 2003 in order to debug it. Since you executed the stored procedure in the previous step, the extproc.exe process should be running. However, if you have not executed the procedure, the process will not be running. For this reason, you should execute the procedure either through SQL*Plus or the Oracle Developer Tools for Visual Studio .NET prior to attempting to debug the code.

To debug the procedure, set a breakpoint in the source code as illustrated here:

Next, select Tools → Debug Processes from the Visual Studio .NET 2003 menu bar. This will produce the Process dialog. Scroll down the list of Available Processes and select extproc.exe:

Click on the Attach button to attach to the extproc.exe process. This produces the Attach to Process dialog:

Because the procedure you developed is a .NET procedure, ensure that the Common Language Runtime checkbox is selected and de-select any other check boxes. Click on the OK button to close the "Attach to Process" dialog and return to the "Processes" dialog. Click on the Close button in the "Process" dialog to begin the debugging process.

In order to initiate the process and fire the breakpoint you must invoke the procedure. In the Oracle Query Window in Visual Studio or from SQL*Plus session call the procedure as follows:

SQL> select GetCountryName('FR') from dual;

This call will invoke the procedure and SQL*Plus will appear to hang. However, the procedure should be stopped at the breakpoint in Visual Studio .NET 2003:

You now debug the procedure as you would normally with stand-alone code.

There is an important difference between debugging with an attached process versus debugging with stand-alone code. Because the extproc.exe process is external to the development environment, the process can terminate or be killed without the debugging environment detecting this situation. In this case, you will see an error such as this:

SQL> select GetCountryName('FR') from dual
       *
ERROR at line 1:
ORA-28576: lost RPC connection to external procedure agent
ORA-06512: at SYS.DBMS_CLR, line 234
ORA-06512: at HR.GETCOUNTRYNAME, line 7

Conclusion

In this introductory look at .NET stored procedure support in Oracle Database 10g Release 2 for Windows, you have learned how .NET stored procedures are supported, how to configure the Oracle networking components, how to develop and deploy a .NET stored procedure, and how to debug a deployed procedure. You now should be ready to begin to develop and deploy .NET stored procedures in your environment.

Send us your comments