.NET CLR stored procedures within Oracle database: Another breaking revolution
(Page 1 of 5 )
In this article, I shall introduce you to developing, deploying and testing a .NET based CLR stored procedure with Oracle database using Visual Studio.NET.
A downloadable file for this article is available
here.
Powerful databases for “extended” Stored Procedures
I am already contributing a series on “Oracle Database Interaction Using ODP.NET and ASP.NET.” While working on the series, I happened to look into the details of new features introduced in Oracle 10g Release 2. It is really fascinating, so I decided to come up with an introductory article on one of the most important features introduced with Release 2.
Every programmer would definitely agree that “Stored Procedures” are very important at the data access tier, as they boost the performance of applications to the optimum level. Almost every RDBMS existing today supports stored procedures in one way or another.
To work with stored procedures for a particular RDBMS, we need to study and work with the “native” database-supported data access language (with its own syntax and semantics). Examples include T-SQL (or Transact SQL) for Microsoft SQL Server 2000, PL/SQL for Oracle, and so on. That is already an old story. Day by day, major RDBMS products are trying to expand beyond the “nativeness,” providing flexibility and integration with other products, along with high security and performance.
The trend of “stored procedures” expanded even to the capability of supporting “external language” based stored procedures! Even though it is good news for the programmers, it would be a great challenge for database administrators and database designers or architects from now on. Just imagine how much time they need to spare for learning and working with the “external languages.”
As I am trying to focus on the Oracle database in this article, let us examine its capabilities. Everyone knows that Oracle database supports native stored procedures with the help of PL/SQL. Oracle started supporting Java (external language) stored procedures from Oracle version 8i onwards. And now, it has further expanded its capability, even to the .NET based CLR stored procedures with Oracle Version 10.2 (Version 10g with Release 2)!
Now you can develop stored procedures in any .NET compliant languages (like VB.NET, C#, and so on) and deploy them in an Oracle database, as if they were native stored procedures. Best of all, the development and deployment has been made quite simple, as Oracle released tools for Visual Studio.NET IDE, which are tightly integrated directly with Oracle database. Now the stored procedures can be developed with the “intellisense” features of Visual Studio.NET
Another RDBMS, Microsoft SQL Server 2005 (which was scheduled to launch in November 2005) has already started supporting “.NET CLR stored procedures” (along with T-SQL stored procedures). I contributed an article (very long ago) on developing and deploying a .NET CLR stored procedure using Visual studio.NET 2005 at the following link:
http://www.aspfree.com/c/a/MS-SQL-Server/Implementing-Managed-code-in-SQL-
Server-2005-using-Visual-Studio-NET-2005/
Okay. Now let us start by connecting to Oracle 10g using Visual Studio.NET.
Next: Connecting to Oracle 10g (Release 2) from Visual Studio.NET using Oracle Explorer >>
More .NET Articles
More By Jagadish Chaterjee
