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 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.
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 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. |
.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:
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.
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.
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) ) )
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 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 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 |