The Oracle Database Extensions for .NET is a new feature of Oracle Database 10g Release 2 on Windows that makes it easy to develop, deploy, and run stored procedures and functions written in any .NET-compliant language.
Oracle Database Extensions for .NET makes it possible to build and run any .NET-based stored procedures or functions with Oracle Database for Microsoft Windows. This feature is supported only from Oracle 10g version 2 (on Windows) onwards or Oracle 10g Express Edition (or Oracle 10g XE).
How come Oracle understands .NET? Oracle database doesn't need to understand .NET at all. It simply hosts the Microsoft .NET 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 without any hurdles.
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.
Once the caller (or other PL/SQL stored procedures, packages, etc.) calls any of these .NET routines (stored procedures or functions), they get executed by the Oracle hosted Microsoft CLR and the results are automatically picked up by the Oracle PL/SQL engine. Once the control comes back to PL/SQL engine, it proceeds with the normal and traditional the PL/SQL process flow of execution.
To develop .NET CLR-based stored procedures or functions, you may need to have Oracle 10g version 2 or higher (for Windows) or at least Oracle 10g Express Edition together with Oracle Database Extensions for .NET installed. If you use Oracle 10g Express Edition, the extensions get automatically installed. But, if you install Oracle 10g version 2 (for Windows), you may have to go to custom install and select the extensions. Apart from the extensions, you also need to download Oracle Developer Tools for Visual Studio (with appropriate version) to develop and deploy .NET CLR-based routines in Oracle database.
Application developers build .NET stored procedures or functions using any .NET compliant language, such as C# and VB.NET, into a .NET assembly (generally a DLL), typically using Microsoft Visual Studio .NET 2003/2005. Obviously, we use Oracle Data Provider for .NET (ODP.NET) in .NET stored procedures and functions for Oracle 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.
Once the .NET stored procedure gets deployed, the PL/SQL wrappers for all of those routines get automatically created within the schema. The user invokes a .NET stored procedure or function through this PL/SQL wrapper (which would be the same as for normal PL/SQL stored procedures or functions). 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.