Oracle Database Interaction Using ODP.NET and ASP.NET: Accessing PL/SQL Stored Procedures
(Page 1 of 4 )
This article (part six of this series) mainly concentrates on working with Oracle PL/SQL stored procedures together with ODP.NET using ASP.NET.
A downloadable file for this article is available
here.
The sample downloadable solution (zip) is entirely developed using Visual Studio.NET 2003 Enterprise Architect on Windows Server 2003 Standard Edition together with Oracle 10g (version 10.2). But, I am confident that it would work with other versions of Windows (which support .NET 1.1) as well.
Sub-programs, stored procedures, PL/SQL stored procedures and hell
Almost every programming language supports sub-programs. They are part of structured programming. A sub-program is a part of a main program separated completely from the main program. We can consider it to be a separate block of statements (with its own declarations and programming statements), still under the control of the main program.
Every sub-program is a separate block, separately named, and the main issue is, it gets separately called by the main block (or even an external program or application). The main block (or external program) calls the sub-program by its name to execute its set of statements. This may be a part of the conditions as well. That means the main program (or external application) may or may not call the sub-program based on certain conditions.
This concept is mainly taken from the initiative of modularization. It is the process by which you break up large blocks of code into smaller pieces called modules (or sub-programs), which can be called by other modules. With the concept of modularization, our code becomes more reusable, more manageable, more readable and finally more reliable. This is not only for sub-programs; it is being implemented in the form of packages (to be dealt with in my upcoming articles) as well.
There are mainly two kinds of sub-programs: procedures and functions. Both are very similar in functionality and syntactical issues. The one difference is that a function returns a value, but a procedure doesn’t.
Until now, you have probably understood everything. Then, what about “stored procedures”? A stored procedure is very similar to a procedure (sub-program), except that it gets directly stored within the database. As PL/SQL is the main programming language used to maintain an Oracle database, you can start writing stored procedures using PL/SQL. Not only that, Oracle even supports stored procedures using Java and .NET too. . NET based stored procedures are supported only from Oracle 10g version 10.2 (by installing Oracle data extensions for .NET) and Microsoft SQL Server 2005.
As a PL/SQL stored procedure involves the most traditional way of writing stored procedures, we concentrate on this. For more information on PL/SQL programming, you can go through my huge lengthy series of PL/SQL articles at www.devshed.com/cp/bio/jagadish-chatarji.
Next: Working with a simple PL/SQL procedure and executing it using ODP.NET >>
More ASP.NET Articles
More By Jagadish Chaterjee
