.NET CLR stored procedures within Oracle database: Another breaking revolution - Testing an Oracle based .NET CLR stored procedure using “iSQL*Plus”
(Page 5 of 5 )
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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
