1 Introduction to Oracle Database Extensions for .NET

This chapter introduces Oracle Database Extensions for .NET, which makes it possible to build and run .NET stored procedures or functions with Oracle Database for Microsoft Windows.

This chapter contains these topics:

Oracle Database Extensions for .NET Overview
Oracle Database Extensions for .NET Architecture
Oracle CLR Host
Oracle Deployment Wizard for .NET
Oracle Data Provider for .NET
Oracle Developer Tools for Visual Studio .NET

Oracle Database Extensions for .NET Overview

Oracle Database Extensions for .NET provides the following:

A Common Language Runtime (CLR) host for Oracle Database
Data access through Oracle Data Provider for .NET classes
Oracle Deployment Wizard for Visual Studio .NET

The Oracle Database hosts the Microsoft Common Language Runtime (CLR) in an external process, outside of the Oracle database process, but on the same computer. The integration of Oracle Database with the Microsoft Common Language Runtime (CLR) enables applications to run .NET stored procedures or functions on Oracle Database, on Microsoft Windows 2003, Windows 2000, and Windows XP.

Application developers can write stored procedures and functions using any .NET compliant language, such as C# and VB.NET, and use these .NET stored procedures in the database, in the same manner as other PL/SQL or Java stored procedures. .NET stored procedures can be used from PL/SQL packages, procedures, functions, and triggers.

Application developers build .NET procedures or functions into a .NET assembly, typically using Microsoft Visual Studio .NET. Oracle Data Provider for .NET is used in .NET stored procedures and functions for data access. After building .NET procedures and functions into a .NET assembly, developers deploy them in Oracle database, using the Oracle Deployment Wizard for .NET, a component of the Oracle Developer Tools for Visual Studio .NET.

The .NET stored procedure or function appears to the caller as a PL/SQL stored procedure or function because a PL/SQL wrapper has been generated for it. The user invokes a .NET stored procedure or function through this PL/SQL wrapper. Oracle Deployment Wizard for .NET determines the probable mappings between Oracle data types and .NET data types, which the user can override. The mappings are handled seamlessly by the PL/SQL wrapper.

Oracle Database Extensions for .NET Architecture

Description of the illustration clrnt001.gif

This architecture diagram shows the client application and then two Oracle spaces, the Oracle process space and the external process space.

The Oracle process space includes the Oracle database instance and hosts the PL/SQL wrapper.

The external process space includes the Oracle CLR host, in which .NET stored procedures or functions are executed.

Oracle CLR Host

Oracle CLR host is installed as part of Oracle Database Extensions for .NET installation and runs in the extproc process. The extproc process loads the Oracle CLR host which in turn loads an instance of the Microsoft Common Language Runtime (CLR), thus providing an interface for the wrapped PL/SQL procedure. These mechanics are not visible to the users. From a user's point of view, the application is invoking just another PL/SQL stored procedure or function.

Note:

The Microsoft .NET Framework must be installed on the same computer as the database.

External Processes

.NET stored procedures or functions are hosted in a process external to the Oracle database. This external process is a heterogeneous service agent called extproc, external procedure agent, or external process. This guide uses the terms extproc process or extproc agent.

The extproc process supports the following architectures:

Dedicated Agent Architecture
Multithreaded Agent Architecture

Dedicated Agent Architecture

In dedicated (that is, single-threaded) agent architecture, an extproc process is started up for each user session. The process terminates when the user session ends. This architecture can consume an unnecessarily large amount of system resources since, with every user session, a new extproc process must be started and shut down. Therefore dedicated agent architecture does not perform well in terms of system resources and runtime efficiency.

Multithreaded Agent Architecture

A multithreaded extproc process uses a pool of shared threads. The tasks requested by the user sessions are put on a queue and are picked up by the first available thread.

Multithreaded agent architecture allows more efficient use of system resources than dedicated architecture.

A separate multithreaded extproc process must be started for each system identifier (SID). Each TNS listener that is running on a system listens for incoming connection requests for a set of SIDs. If the SID in an incoming Oracle Net connect string is one that the listener is listening for, then that listener processes the connection. If a multithreaded process has been started for the SID, then the listener passes the request to that process.

See Also:

Real Application Clusters (RAC) and External Processes

The Oracle multithreaded extproc process is tightly coupled with the Oracle listener. Therefore, each node in a Real Application Clusters (RAC) environment has an Oracle multithreaded extproc process associated with the listener on that node.

Multiple Databases Instance and External Processes

A single Oracle multithreaded extproc process is used with multiple database instances associated with a single Oracle Home if a single listener is shared among multiple database instances.

Listener and Tnsnames Files

The following are typical examples of the listener.ora and tnsnames.ora files configured for Oracle Database Extensions for .NET. By default, Oracle Database Extensions for .NET uses CLRExtProc as the SID, but this can be changed using the Database Configuration Assistant (DBCA).

Listener.ora file

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\database_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oracle\database_1)
      (PROGRAM = extproc)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
     
    )
  )

Tnsnames.ora File

ORACLE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = user.us.oracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle.us.oracle.com)
    )
  )
 
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
 
MSOLNIT-PC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = msolnit-pc)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
 
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

Oracle Deployment Wizard for .NET

Description of the illustration TitlePage.gif

The Oracle Deployment Wizard for .NET is a graphical tool integrated with Microsoft Visual Studio .NET which makes it easy to deploy any .NET procedure or function into an Oracle database. It is installed as part of Oracle Developer Tools for Visual Studio .NET.

See Also:

Oracle Developer Tools for Visual Studio .NET Dynamic Help, available by installing Oracle Developer Tools for Visual Studio .NET, for more information

Oracle Data Provider for .NET

Oracle Data Provider for .NET provides data access to the Oracle database from any client application. Oracle Data Provider for .NET is available for free download on Oracle Technology Network (OTN).

See Also:

Oracle Data Provider for .NET Developer's Guide for detailed descriptions of ODP.NET classes

Oracle Developer Tools for Visual Studio .NET

Oracle Developer Tools for Visual Studio .NET is a set of application tools tightly integrated with the Visual Studio .NET development environment. Oracle Developer Tools enables developers to execute a wide range of application development tasks, such as creating tables, editing stored procedures, and viewing data in the Oracle Database. Oracle Developer Tools for Visual Studio .NET is available for free download on Oracle Technology Network (OTN).

See Also:

Oracle Developer Tools for Visual Studio .NET Help