Oracle Database Interaction Using ODP.NET and ASP.NET: Accessing PL/SQL Stored Procedures

This article (part six of this series) mainly concentrates on working with Oracle PL/SQL stored procedures together with ODP.NET using ASP.NET.

Contributed by Jagadish Chaterjee
Rating:

/ 15
October 27, 2005

Rate this Article:
MEH MEH++

SEARCH ASP FREE

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.

Working with a simple PL/SQL procedure and executing it using ODP.NET

Let us start creating a PL/SQL procedure. You can do this in several ways. It includes using SQL*Plus, iSQL*Plus or even “Oracle Developer tools for Visual Studio.NET”. In my case I used “Oracle Developer tools for Visual Studio.NET” which is an excellent add-in provided by Oracle to integrate with Visual Studio.NET. I need not leave the Visual Studio.NET environment to work with an Oracle database, if I install it. And most of all, it is completely FREE. You can download it from http://www.oracle.com/technology/tech/dotnet/tools/index.html.

The simple PL/SQL procedure I created for demonstrating this example is as follows:

PROCEDURE "SCOTT"."P_INCREASE_SALARY" AUTHID CURRENT_USER IS

BEGIN
update emp set sal = sal + 500;
commit;

END "P_INCREASE_SALARY";

Because 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_INCREATE_SALARY” and it increases all the salaries of employees (in EMP table) by 500. It is always a good practice to issue a “commit” statement after a DML statement (to remove unnecessary locks after successful database manipulations).

Now let us see how to execute the stored procedure from ASP.NET using ODP.NET.

        Dim cn As New OracleConnection("User
ID=scott;password=tiger;Data Source=ORCL")
        Try
            Dim cmd As New OracleCommand("P_INCREASE_SALARY", cn)
            cmd.CommandType = CommandType.StoredProcedure
            cn.Open()
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            Me.lblError.Text = "Succesfully executed.."
        Catch ex As Exception
            Me.lblError.Text = ex.Message
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
End Try

Within the above code fragment, I created an “OracleConnection” object and “OracleCommand” object. “OracleCommand” is assigned with our previous stored procedure named “P_INCREASE_SALARY”. The most important statement to concentrate on here is the following:

cmd.CommandType = CommandType.StoredProcedure

The above statement confirms that the “OracleCommand” needs to execute a stored procedure (and not a query or DML statement). That’s all. The rest will be automatically taken care by ODP.NET, ASP.NET and the Oracle database!

A simple PL/SQL procedure with a single parameter

In the previous section, we looked at a simple PL/SQL procedure (without any parameters). Now, we shall work on the same, but with a simple and single numeric parameter. Let us see the stored procedure first.

PROCEDURE "SCOTT"."P_INCREASE_SALARY" (
"INC_AMT" IN NUMBER) IS
BEGIN
update emp set SAL = SAL + "INC_AMT";
commit;
END "P_INCREASE_SALARY";

Any programmer would definitely understand the concept of parameters to a procedure. So, I don’t think that I need to explain much about the above stored procedure. In brief, the above stored procedure simply accepts a numeric value into the parameter variable named “INC_AMT” (incremental amount) and finally updates the salary with that incremental amount. You will find more about “IN” later in this section. So, now let us delve into the details of our ASP.NET code.

        Dim cn As New OracleConnection("User
ID=scott;password=tiger;Data Source=ORCL")
        Try
            Dim cmd As New OracleCommand("P_INCREASE_SALARY", cn)
            cmd.CommandType = CommandType.StoredProcedure
            Dim p_inc_sal As New OracleParameter("inc_sal",
OracleDbType.Int16, arameterDirection.Input)
            p_inc_sal.Value = 200
            cmd.Parameters.Add(p_inc_sal)

            cn.Open()
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            Me.lblError.Text = "Succesfully executed.."
        Catch ex As Exception
            Me.lblError.Text = ex.Message
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

Even the above code fragment is very similar to the one available in the previous section. The only difference will be the following set of statements:

            Dim p_inc_sal As New OracleParameter("inc_sal",
OracleDbType.Int16, ParameterDirection.Input)
            p_inc_sal.Value = 200
            cmd.Parameters.Add(p_inc_sal)

I already introduced the “OracleParameter” class in my previous set of articles. I request you go through them, if you would like to delve into this in greater depth. According to the above code fragment, I just created an “OracleParameter” object with a parameter name (which is the same as the stored procedure name) along with its data type and direction type.

“ParameterDirection” is basically an enumeration, which contains all types of parameter directions, which may be used with every “OracleParameter”. In this case, our application is “sending” (or INPUT to oracle stored procedure) a value to the stored procedure, and thus I used “ParameterDirection.Input”. We shall see about the other direction types later in my upcoming articles.

A simple PL/SQL procedure with multiple parameters

In this section, we shall work with more than one parameter (or multiple parameters). I try to use the same stored procedure as above, but with one additional parameter. The stored procedure would look something like the following:

PROCEDURE "SCOTT"."P_INCREASE_SALARY" (
"INC_AMT" IN NUMBER,
"DNO" IN NUMBER) IS
BEGIN
update emp set sal = sal + "INC_AMT" WHERE deptno = "DNO";
commit;
END "P_INCREASE_SALARY";

Within the above stored procedure, I added “DNO” as a new parameter and I am using it within the UPDATE statement. That means we need to provide two parameter values, and the UPDATE statement increases the salaries (based on INC_AMT) for all the employees working in the specified DNO. Now let us look into ASP.NET code to execute the above stored procedure.

            Dim p_inc_amt As New OracleParameter("inc_amt",
OracleDbType.Int16, ParameterDirection.Input)
            p_inc_amt.Value = 200
            cmd.Parameters.Add(p_inc_amt)

            Dim p_dno As New OracleParameter("dno",
OracleDbType.Byte, ParameterDirection.Input)
            p_dno.Value = 20
            cmd.Parameters.Add(p_dno)

I hope you can understand the above code very easily. Now, I am working with two “OracleParameter” objects (for “inc_amt” and “dno”). Finally, we add those two “OracleParameter” objects to the “OracleCommand” object and execute it.

There is another simple way to do the same as above. This removes a little bit of the burden of typing lengthy code!

cmd.Parameters.Add(New OracleParameter("INC_AMT",
200))
cmd.Parameters.Add(New OracleParameter("DNO", 20))

This method is preferable, when you are confident about the intrinsic conversions of data types. If you are not confident enough about the data type conversions, it is better to use the first method, so that you can explicitly define the data types along with values. Of course, there is another way, too, as follows:

cmd.Parameters.Add("INC_AMT", OracleType.Number).value=200
cmd.Parameters.Add("DNO", OracleType.Number).value=20

Just for the purpose of demonstration, I used very simple stored procedures. But, in general, you would never use such types of stored procedures in any production environment.

Any comments, suggestions, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.