Skip Headers
Oracle® Database Extensions for .NET Developer's Guide
11g Release 2 (11.2) for Microsoft Windows

Part Number E17724-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

2 Installation and Configuration

This chapter describes the installation of Oracle Database Extensions for .NET, system requirements, and file locations.

This chapter contains these topics:

System Requirements

Each release of Oracle Database Extensions for .NET has very specific version requirements. The following system requirements only apply to 32-bit Oracle Database Extensions for .NET version 11.2.0.1.0. If you are using a different version, please see the documentation specific to your version:

Requirements for .NET Stored Procedures and Functions

A .NET stored procedure or function must meet the following requirements:

File Locations after Installation

OraClr11.dll is installed in the ORACLE_BASE\\ORACLE_HOME\bin directory.

Oracle.Database.Extensions.dll is installed to the following locations:

The readme file, readme.html, is installed in the ORACLE_BASE\\ORACLE_HOME\ODE.NET\DOC directory.

.NET assemblies deployed by developers are copied into the ORACLE_BASE\ORACLE_HOME\bin\CLR directory (or its subdirectory) by the Oracle Deployment Wizard for .NET.

Installation and First Use

Oracle Database Extensions for .NET is installed as part of a custom Oracle Database installation using the Oracle Universal Installer. From the Oracle Universal Installer Welcome screen, you must chose Advanced Installation, then from the Select Installation Type screen, choose Custom. When the Available Product Components list is displayed, go to Enterprise Edition Options, and select Oracle Database Extensions for .NET.

Oracle Database Extensions for .NET is configured using the Database Configuration Assistant.

Configuring Extproc Agent Using Windows Service

As part of Oracle Database Extensions for .NET installation, a Windows service is installed. The service is called OraClrAgnt and can be accessed through the Service Control Panel, as OracleORACLE_HOMEClrAgent, where ORACLE_HOME represents your Oracle home.

This service is used for the startup, configuration, and shutdown of the extproc agent.

The parameters which can be configured using this service are listed in Table 2-1, "OraClrAgnt Service Parameters".

These parameter values can be specified as part of the Start Parameters in the properties window of the Control Panel Service. In this case, the parameter values are not saved and the values must be supplied again if the service is restarted later.

To persist the parameter values, you can change the Windows registry entry for this service and provide the parameter values as command line parameters to OraClrAgnt.exe. To do this, set the Windows registry key, ImagePath, located at

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ OracleOracleHomeClrAgent

The value should be something similar to the following:

ORACLE_BASE\\ORACLE_HOME\bin\OraClrAgnt.exe agent_sid=CLRExtProc max_dispatchers=2 tcp_dispatchers=0 max_task_threads=6 max_sessions=25 ENVS="EXTPROC_DLLS=ONLY:ORACLE_BASE\\ORACLE_HOME\bin\oraclr11.dll"

If the service cannot be started or stopped, the error messages are logged in the Application Log of the Event Viewer, with the service name as the event source name.

OraClrAgnt Service Parameters

Table 2-1 lists the parameters which can be configured using this service.

Table 2-1 OraClrAgnt Service Parameters

Parameters Descriptions

agent_sid

This represents the SID of the extproc process. The default value is CLRExtProc. This is a mandatory parameter. If this parameter value is changed, appropriate changes need to be made in tnsnames.ora and listener.ora files.

ENVS

Variable that specifies the EXTPROC_DLLS environment variable, which restricts the DLLs that extproc can load.

This is similar to setting environment variables to external procedures using listener.ora.

Refer to "Table 13–5 External Procedures Settings in listener.ora" in Oracle Net Services Administrator's Guide for more information.

listener_address

Address on which the listener is listening. This is an optional parameter. If it is not specified, then this is set to the default value.

max_dispatchers

Number of maximum dispatchers in the extproc process. This is an optional parameter. If it is not specified, then this number is set to a default value.

max_sessions

Number of maximum sessions in the extproc process. This is an optional parameter. If it is not specified, then this number is set to a default value.

max_task_threads

Number of maximum task threads in the extproc process. This is an optional parameter. If it is not specified, then this number is set to a default value.

shutdown_address

Address on which the agent should listen for shutdown messages from agtctl. This is an optional parameter. If it is not specified, then this is set to the default value.

tcp_dispatchers

Number of TCP dispatchers in the extproc process. This is an optional parameter. If it is not specified, then this number is set to a default value.


Note:

By default, this service is created and run in the Local System Account; however, it can be changed to work with a logged-on user account through the service control panel.

See Also:

Oracle Database Application Developer's Guide - Fundamentals, Table A-2, Configuration Parameters for agtctl

Tuning OraClrAgnt for Performance

You should tune the OraClrAgnt to match the expected load on your system.

Excessive extproc.exe processes being spawned is a sign that you have set the configuration values too low.

Start with the following values and increase as you test your system for performance:

OraClrAgnt Parameter Initial Value
max_sessions 25
max_task_threads 6
max_dispatchers 2

Migrating .NET Stored Procedures from Oracle Database 10.2 to Oracle Database 11.2

You can migrate .NET stored procedures from Oracle Database release 10.2 to release 11.2 as follows:

  1. Select the libraries that are used by .NET stored procedures from the Oracle Database 10g release 2 (10.2) database. For example,

    SELECT library_name, file_spec FROM ALL_LIBRARIES WHERE OWNER='SYS' and FILE_SPEC LIKE '$ORACLE_HOME\bin\clr\%';

    library_name is usually in the format dll_name_DLL. For example, the library_name for Project1.dll would be PROJECT1_DLL.

  2. Create a SQL file manually (for example, DotNetSP_Grant.sql) with the following SQL statements:

    CREATE LIBRARY "SYS"."library_name" AS 'file_spec'
    GRANT EXECUTE ON "SYS"."library_name" TO "schema_name"
    GRANT EXECUTE ON "SYS"."DBMS_CLR" TO "schema_name"
    GRANT EXECUTE ON "SYS"."DBMS_CLRTYPE" TO "schema_name"
    GRANT EXECUTE ON "SYS"."DBMS_CLRPARAMTABLE" TO "schema_name"
    
  3. Run Oracle Data Pump Export utility for the Oracle Database 10g release 2 (10.2).

    Expdp system schemas="schema_name" directory=ORACLECLRDIR dumpfile=DotNetSP.dmp include=PROCEDURE,FUNCTION
    
  4. Copy .NET stored procedure assemblies from Oracle Database 10g release 2 (10.2) ORACLE_BASE\\ORACLE_HOME\bin\clr folder and its subfolders to the same directory structure in Oracle Database 11g release 2 (11.2).

  5. Run DotNetSP_Grant.sql as SYSDBA against the Oracle Database 11g release 2 (11.2) database.

  6. Run Oracle Data Pump Import utility for the Oracle Database 11g release 2 (11.2) database.

    impdp system schemas="schema_name" directory=ORACLECLRDIR dumpfile=DotNetSP.dmp
    

Mandatory Migration of .NET 1.x Stored Procedures to .NET 2.0

Beginning with Oracle Database Extensions for .NET version 11.1.0.7.20, .NET 1.x stored procedures are no longer supported. Specifically, Oracle Database Extensions for .NET 1.x and Oracle Data Provider for .NET 1.x are no longer included in this release. If you have existing .NET 1.x stored procedures from an earlier release, you will need to take special migration steps to ensure that they work in this release.

WARNING:

In some cases, this migration will require code changes. You should not install this release in a production environment if you have .NET 1.x stored procedures until you have verified in a test environment that your stored procedures have been successfully migrated. If you have already installed this release and are encountering errors in your .NET 1.x stored procedures, you should downgrade to an earlier version of Oracle Database Extensions for .NET until you are able to make any required code changes to your stored procedures.

Determining if Code Changes are Needed

You will need to analyze your .NET 1.x stored procedures to determine if code changes are required to migrate to this release. Specifically you should investigate:

Migration Approaches

There are two possible approaches to allow your .NET 1.x stored procedures to work with this release:

Addressing Code Incompatibilities Between ODP.NET for .NET 1.x and ODP.NET for .NET 2.0

You may need to address the following code incompatibilities related to ODP.NET in your .NET 1.x stored procedures:

  • In ODP.NET for .NET 2.0, OracleParameter.Value returns OracleDecimal instead of .NET native types when OracleParameter.OracleDbType is set to a number type, such as Int32, Double. This behavior change is summarized in the following table:

    OracleParameter.OracleDbType OracleParameter.Value Returned in .NET 1.x OracleParameter.Value Returned in .NET 2.x
    OracleDbType.Byte System.Byte OracleDecimal
    OracleDbType.Double System.Double OracleDecimal
    OracleDbType.BinaryDouble System.Double OracleDecimal
    OracleDbType.Int16 System.Int16 OracleDecimal
    OracleDbType.Int32 System.Int32 OracleDecimal
    OracleDbType.Int64 System.Int64 OracleDecimal
    OracleDbType.Single System.Single OracleDecimal
    OracleDbType.BinaryFloat System.Single OracleDecimal

    If any of the preceding OracleDbType enumeration values are used by your .NET 1.x stored procedure for an out or in/out OracleParameter, then it may need to be modified.

  • In ODP.NET for .NET 2.0, OracleParameter.Value returns provider-type specific null value (OracleClob.Null) instead of DBNull.Value when OracleParameter.OracleDbType is set for connected types. For example, if OracleParameter.OracleDbType is set to OracleDbType.Clob, then OracleParameter.Value represents a null value by returning OracleClob.Null instead of DBNull.Value, which is the case in ODP.NET for .NET 1.x.

Addressing Code Incompatibilities Between Oracle Database Extensions for .NET Versions 1.x and 2.0

If any of the connected types are passed as parameters to your .NET 1.x stored procedure, and if the procedure checks for null values, then you may need to modify the procedure. Oracle Database Extensions for .NET 1.x uses a .NET null to represent a null value when the parameter happens to be a connected type. Oracle Database Extensions for .NET 2.0 uses provider-type specific null value, such as OracleBFile.Null, in these cases.

You can configure Oracle Database Extensions for .NET 2.0 to use .NET null values for connected type null values in place of provider-specific type null values. To do this, create and set the following registry value to 0:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ORACLE_HOME\ODE\ProviderNull

You can find the documentation about this registry value in the section "Backward Compatibility for Nullable ODP.NET Connected Types" in Oracle Data Provider for .NET Developer's Guide.

Recompile and Redeploy .NET 1.x Stored Procedures Using ODP.NET for .NET 2.0

If you find code incompatibilities, you will need to recompile your .NET 1.x stored procedures using ODP.NET for .NET 2.0.

Even if you do not find code incompatibilities, Oracle recommends that you recompile and redeploy your .NET 1.x stored procedures. Recompiling ensures that you do not get run-time errors, if there are any unaddressed compatibility issues. Redeploy the stored procedures after successful recompilation.

Configure .NET 1.x Stored Procedures Using ODP.NET for .NET 2.0

Alternatively, if there are no code incompatibilities, you can configure the .NET 1.x stored procedures to run with ODP.NET for .NET 2.0. This approach does not require you to recompile and redeploy the .NET stored procedures. However, you might get run-time errors, if there are any unaddressed incompatibilities between versions 1.x and 2.0 of Oracle Database Extensions for .NET, ODP.NET, and ADO.NET. Use the following steps to configure the .NET 1.x stored procedures to run with ODP.NET for .NET 2.0 without recompiling and redeploying them:

  1. If the .NET 1.x stored procedures exist in an old Oracle home, then copy the .NET 1.x stored procedure DLLs to the new Oracle home, under NewOracleHome\bin\clr.

    If the stored procedures were originally deployed in a sub-folder of the previous Oracle home, such as OldOracleHome\bin\clr\Accounts, then create a similar directory structure under the new Oracle Home, such as NewOracleHome\bin\clr\Accounts,and copy the .NET 1.x stored procedure DLLs into it.

  2. Modify or create the extproc.exe.config file in the NewOracleHome\bin folder to redirect ODP.NET (Oracle.DataAccess.dll) 1.x references to the installed version of ODP.NET for .NET 2.0. For example, to redirect ODP.NET 1.111.6.20 references to ODP.NET 2.111.7.20, the extproc.exe.config file should include the following configuration section:

    <configuration> 
      <runtime> 
        <legacyUnhandledExceptionPolicy enabled="1"/> 
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
          <dependentAssembly>
            <assemblyIdentity name="Oracle.DataAccess"
                              publicKeyToken="89b483f429c47342"
                              culture="neutral" />
            <bindingRedirect oldVersion="1.111.6.20"
                             newVersion="2.111.7.20"/>
          </dependentAssembly>
        </assemblyBinding>
      </runtime>
    </configuration>
    

Oracle Database Extensions for .NET Registry Options

You can add functionality to Oracle Database Extensions for .NET using Windows registry entries that are located at

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ORACLE_HOME\ODE

Table 2-2 lists registry keys that add functionality to Oracle Database Extensions for .NET and the sections where the keys are discussed.

Table 2-2 Registry Options

Registry Key Section

.NETFramework

"Selecting a .NET Run Time Version"

ProviderNull

"Backward Compatibility for Nullable ODP.NET Connected Types"

RecreateAppDomain

"Unloading .NET Assemblies for Easy Redeployment"

TraceFileName

"TraceFileName"

TraceOption

"TraceOption"

TraceLevel

"TraceLevel"


Unloading .NET Assemblies for Easy Redeployment

From release 11.1.0.6.20, you can unload .NET assemblies when .NET stored procedure execution completes. This makes it easier to repeatedly test your code during development. If this registry key is not enabled, the exproc.exe process must be stopped and started with each redeployment.

This feature should not be used during performance testing or for production, as it has a negative effect on performance.

To define assembly loading behavior, set the registry value RecreateAppDomain of type REG_SZ under this registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ORACLE_HOME\ODE

The valid values for RecreateAppDomain are:

0 = .NET Assembly remains loaded when the .NET stored procedure execution completes.

1 = .NET Assembly is unloaded when the .NET stored procedure execution completes.

Backward Compatibility for Nullable ODP.NET Connected Types

ODP.NET for .NET 2.0 supports a static Null property in ODP.NET Connected Types, in addition to the existing support for disconnected types such as OracleDecimal. It also supports a public property, IsNull, for each of these types to check whether or not objects of these types have been assigned a value.

See Also:

Oracle Data Provider for .NET Developer's Guide for more information on nullable types

This enables Null objects of ODP.NET Connected Types to be propagated to and from a .NET stored procedure. The list of these connected types follows:

  • OracleBlob

  • OracleClob

  • OracleBFile

  • OracleXmlType

Previous versions of .NET stored procedures expected ODP.NET connected type parameters to be passed as NULL rather than a Type.Null object. In order to support backward compatibility, the registry string ProviderNull can be used to retain the old behavior.

To determine how Oracle Database Extensions for .NET handles passing a NULL value to an ODP.NET connected type parameter in a .NET stored procedure, set the registry string ProviderNull under this registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ORACLE_HOME\ODE

The valid values for ProviderNull are:

0 = ODP.NET connected-type parameters are passed as NULL rather than Type.Null object.

1 = Oracle Database Extensions for .NET passes a Type.Null object to the .NET stored procedure in the case of a null value.

Selecting a .NET Run Time Version

If multiple .NET run time versions are installed on the database computer, then Oracle Database Extensions for .NET defaults to the latest .NET run time available. However, you can configure Oracle Database Extensions for .NET to load a particular .NET run time by setting a registry value.

To specify .NET run time version, set the registry value, .NETFramework under this registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ORACLE_HOME\ODE

Set the registry value to the appropriate .NET run time version, for example, v2.0.50727.

Note: .NET framework 1.x is not supported in this release. For more information see: Mandatory Migration of .NET 1.x Stored Procedures to .NET 2.0.

Debug Tracing

Oracle Database Extensions for .NET provides debug tracing support, which allows logging of all the Oracle Database Extensions for .NET activities into a trace file. Different levels of tracing are available.

The following registry settings should be configured under

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ORACLE_HOME\ODE

TraceOption

TraceOption specifies whether to log trace information in single or multiple files for different threads. If a single trace file is specified, the filename specified in TraceFileName is used. If the multiple trace files option is requested, a Thread ID is appended to the file name provided to create a trace file for each thread.

The valid values for TraceOption are:

0 = Single trace file

1 = Multiple trace files

Note: You can use Oracle Data Provider for .NET tracing mechanism to troubleshoot ODP.NET specific issues.

TraceFileName

TraceFileName specifies the file name that is to be used for logging trace information. If TraceOption is set to 0, the name is used as is. However, if TraceOption is 1, the Thread ID is appended to the file name provided.

The valid values for TraceFileName are: any valid path name and file name.

TraceLevel

TraceLevel specifies the level of tracing in Oracle Database Extensions for .NET.

The valid values for TraceLevel are:

0 = None

1 = Entry and exit information

See Also:

Debug Tracing section in Oracle Data Provider for .NET Developer's Guide

Unhandled Exceptions

.NET stored procedures are hosted inside the external procedure agent extproc.exe. .NET run time version 2.0 allows most unhandled exceptions in threads to proceed, which might cause an unhandled exception to terminate extproc.exe. This behavior is different from .NET run time versions 1.0 and 1.1, which provide a backstop for many unhandled exceptions.

extproc.exe Config File

Oracle Database Extensions for .NET installs extproc.exe.config file in the ORACLE_BASE\\ORACLE_HOME\Bin directory to force .NET run time version 2.0 to use the behavior of .NET run time version 1.1. If the Garbage Collector or a thread created inside the .NET stored procedures throws an exception, and if this exception is not handled by the .NET stored procedure, then the tracing mechanism reports the exception.

The following is an example extproc.exe.config file:

<configuration>
  <runtime>
    <legacyUnhandledExceptionPolicy enabled="1"/>
  </runtime>
</configuration>

You can change this file to revert back to .NET 2.0 behavior by modifying the extproc.exe.config file as follows:

<configuration>
  <runtime>
   <legacyUnhandledExceptionPolicy enabled="0"/>
  </runtime>
</configuration>