Home.NET .NET CLR stored procedures within Oracle d...
.NET CLR stored procedures within Oracle database: Another breaking revolution
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:
Let us go through the following steps to connect to Oracle 10g using Visual Studio.NET 2003:
Open Visual Studio.NET 2003 IDE
Go to menu View -> Oracle Explorer as shown in Figure 1
Within the Oracle Explorer, right click on Data Connections -> Add Connection (Fig2)
Within the “Add Connection” window, provide the details of “Data Source”, “User Name” and “Password” (as shown in Figure 3). You can test the details by clicking on “Test Connection” button.
Once you click “OK”, Oracle Explorer gets updated as shown in Figure 4.
“Data Source” will be generally the global SID or “Local net Service Name” you configured during Oracle server/client installation. For this demonstration, I am using the “scott” schema (sample schema provided by Oracle) with the password “tiger”.
Once you get connected, you can play with all the items existing within the “Oracle Explorer” along with creating tables, inserting, modifying, deleting, dropping, designing relations, stored procedures (using PL/SQL), and so on. Enjoy playing with it.
Once you complete all of the steps in previous section, proceed with the following steps to develop a .NET CLR based stored procedure using Visual Studio.NET.
Go to File -> New -> Project
Within the “New Project” window, select “Visual Basic Projects” as Project Type “Oracle Project” as template, Name as “CLRSample” and click “OK” (shown in Fig5).
Once you click on “finish” button (from the previous section), you should be able to see the output window of Visual studio.NET as something like the following figure 11.
To execute the .NET CLR stored procedure from within Visual Studio.NET, open “Oracle Explorer” from the “View” menu, open “procedures” of “scott” schema and right click on “incrementsalary” as shown in Figure 12.
In the “Run Procedure” dialog, provide value for “empno” as 7839 and “incrementvalue” as 500 (as shown in Figure 13) and click OK.
Once it is successfully executed, it should display something like the following Figure 14.
Now you can check your “emp” table in the database, and “KING” gets his salary incremented from 5000 to 5500!
Open any browser and go to “http://server:port/isqlplus”. Provide “Username”, “password” and “connect identifier” according to your configuration (as shown in Fig 15) and click “login”.
Once it opens workspace, just type the following code and press “execute”.
Begin
IncrementSalary(7839, 500);
End;
You should see the output something like the following Figure 16.
That’s it. Now you can check your “emp” table, which gets updated accordingly. Actually, the above three lines are nothing but some simple PL/SQL statements, generally used to call (or execute) any stored procedure stored in an Oracle database.
Summary
When writing stored procedures, triggers, and user-defined functions, one decision you must make is whether to use traditional PL/SQL, or a .NET Framework language such as Visual Basic .NET or Visual C# .NET (or even Java). Use PL/SQL when the code will mostly perform data access with little or no procedural logic. Use .NET CLR based (or Java based code) for CPU-intensive functions and procedures that feature complex logic, or when you want to make use of the BCL of the .NET Framework (or Java API).
Another important issue to consider before comparing the two major RDBMS, MS SQL Server 2005 and Oracle 10g, is that Microsoft SQL Server 2005 supports CLR integration based on .NET version 2.0, whereas Oracle 10.2 supports only .NET version 1.1 (at the time of this writing).
Even though I examined only a stored procedure in the above scenario, you can embed .NET CLR stored procedures in PL/SQL Packages also. Having trails with .NET CLR based stored functions, getting them under the packages, and so on, would be enhancements. Check this section regularly for new articles on the same concept.
I developed this entire solution (downloadable here or at the beginning of this article) using Microsoft Visual Studio.NET 2003 Enterprise Architect on Windows Server 2003, standard edition, running Oracle database Version 10g Release 2. I could not go in depth into the CLR details (or even ADO.NET using ODP.NET) as it is beyond the scope of this article. But, if you really need them, you can go through my list of articles related to that subject within this site.
I leave it to the programmers for further enhancements. Any doubts, comments, suggestions, bugs, errors or feedback are welcomed at jag_chat@yahoo.com
.NET CLR stored procedures in SQL Server 2005 DB: Good News for Programmers, Sho
