Oracle Database Interaction Using ODP.NET and ASP.NET: Accessing Stored Procedures, Functions
(Page 1 of 5 )
This article is an extension to my previous article (Accessing P/SQL Stored Procedures). It mainly concentrates on the parameter directions of a PL/SQL stored procedure. It also deals with stored functions and packages.
A downloadable file for this article is located
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.
A simple PL/SQL procedure with OUT parameter
I already introduced the concepts of stored procedures and accessing them using ODP.NET in my previous article. Now, I shall extend the same article with a few more features. Even though I dealt with stored procedures in my previous article, they are a bit basic.
We didn’t focus much on “parameter direction types” in PL/SQL, which is a very important topic. Even though this series mostly concentrates on ODP.NET, this topic is worth discussing here. For a complete understanding of PL/SQL sub programs, I suggest you go through my very long series on “database interaction with PL/SQL” at www.devshed.com/bio/cp/jagadish-chatarji.
Coming to our brief discussion of parameter direction types, let us first concentrate on the OUT parameter. The simple PL/SQL procedure I created for demonstrating this example is as follows:
PROCEDURE "SCOTT"."P_GETCOUNT" (
"RECCOUNT" OUT NUMBER) IS
BEGIN -- executable part starts here
SELECT COUNT(*)
INTO RECCOUNT
FROM EMP;
END "P_GETCOUNT";
The above syntax would be a bit different if you are directly working with SQL*Plus or iSQL*Plus. As I created the above from “Oracle Developer Tools for Visual Studio.NET”, the syntax would be a bit different. But, overall, we can understand that the name of the stored procedure is “P_GETCOUNT” and it finds the number of employees available in the table EMP.
The most important issue to consider is the following parameter declaration:
"RECCOUNT" OUT NUMBER
The above declaration confirms that “RECCOUNT” is a parameter that could return a value (indirectly) back to the application. In fact, it doesn’t accept any parameter at all as input. This means that passing a parameter value is not necessary any more.
Next: Accessing a simple PL/SQL procedure with OUT parameter using ODP.NET >>
More ASP.NET Articles
More By Jagadish Chaterjee
