Oracle Database Interaction Using ODP.NET and ASP.NET: Accessing Stored Procedures, Functions

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.

Accessing a simple PL/SQL procedure with OUT parameter using ODP.NET

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_GETCOUNT", cn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("reccount", OracleDbType.Int32).Direction
= ParameterDirection.Output
    cn.Open()
    cmd.ExecuteNonQuery()
    Me.lblMsg.Text = cmd.Parameters("reccount").Value
    cmd.Dispose()
Catch ex As Exception
    Me.lblMsg.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 name “P_GETCOUNT”.  The most important statements to concentrate on here are the following:

    cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("reccount", OracleDbType.Int32).Direction =
ParameterDirection.Output

The first statement confirms that the “OracleCommand” needs to execute a stored procedure (and not a query or DML statement).  The second statement is the heart, which deals with parameter direction. 

Within the second statement, I am trying to add a parameter “recount” with a “direction” as “ParameterDirection.Output”.  This gets mapped properly with the output parameter specification of our PL/SQL stored procedure above.  If no “ParameterDirection” is specified, it takes the “ParameterDirection.Input” as default.  So, it is very much essential to specify “ParameterDirection.Output” in the above scenario.

That’s all and the rest will be automatically taken care of by ODP.NET, ASP.NET and the Oracle database!  We retrieve value back from the OUT parameter using the following statement:

cmd.Parameters("reccount").Value

What about IN OUT parameter in a PL/SQL procedure?

If we summarize our experience with IN as well as OUT of PL/SQL procedures,  we can understand that IN is used for passing information to a PL/SQL procedure and OUT is used to get back the information from a PL/SQL procedure.

But what about IN OUT?  It is just the combination of both IN and OUT together.  In brief, the same parameter can be used to pass and return the values.  In our example below, we are trying to create a stored procedure with two parameters.  One will be of type IN and the other will be of type IN OUT.  Let us see the stored procedure first.

PROCEDURE "SCOTT"."P_INCREASESALARY" (
"ENO" IN NUMBER,
  "INCSAL" IN OUT NUMBER) IS
  emp_sal emp.SAL%type;
BEGIN
    update emp set sal = sal + "INCSAL"
      where empno = "ENO";
    select sal into "INCSAL" from emp
      where empno = "ENO";
END "P_INCREASESALARY";

Within the above stored procedure, we need to understand several issues.  The name of the stored procedure will be “P_INCREASESALARY”.  It contains two parameters, “ENO” and “INCSAL”.  “ENO” is an IN parameter (which means we can only pass values through it, but not return any values).  “INCSAL” is an IN OUT parameter (which means we can pass and retrieve values).

Within the body of the stored procedure, I am trying to update the particular employee (specified by “ENO”) with an incremental salary (specified by “INCSAL”). After successful updating, I would like to return the latest salary of the same employee back to the application.  For that purpose, I am using a SELECT statement to retrieve the latest salary of the employee and assign it to “INCSAL”.

The wonder is that you need not return it, as you generally do using a function.  It would automatically reflect (as it is similar to a reference pointer) in the calling application -- in this case, it is an ASP.NET application.  I hope that you understood the above stored procedure.  Now, we shall see about accessing it using ODP.NET, in the next section.

Accessing the IN OUT parameter using ODP.NET

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_INCREASESALARY", cn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("ENO", OracleDbType.Int32).Value = 7369

    Dim p_incsal As New OracleParameter("INCSAL",
OracleDbType.Int32)
    p_incsal.Direction = ParameterDirection.InputOutput
    p_incsal.Value = 100
    cmd.Parameters.Add(p_incsal)

    cn.Open()
    cmd.ExecuteNonQuery()
    Me.lblMsg.Text = "Salary after increment: " & cmd.Parameters
("incsal").Value
    cmd.Dispose()
Catch ex As Exception
    Me.lblMsg.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 name “P_INCREASESALARY”.  As you are already familiar with IN parameters, the most important statements to concentrate on here are the following:

Dim p_incsal As New OracleParameter("INCSAL", OracleDbType.Int32)
    p_incsal.Direction = ParameterDirection.InputOutput
    p_incsal.Value = 100
    cmd.Parameters.Add(p_incsal)

I am trying to add a parameter “INCSAL” with a “direction” as “ParameterDirection.InputOutput”.  This gets mapped properly with the IN OUT parameter specification of our PL/SQL stored procedure above.  If no “ParameterDirection” is specified, it takes the “ParameterDirection.Input” as default.  So, it is essential to specify “ParameterDirection.InputOutput” in the above scenario.

That’s all and the rest will be automatically taken care by ODP.NET, ASP.NET and the Oracle database!  We retrieve our IN OUT parameter values as follows:

cmd.Parameters("incsal").Value

A word about stored functions and about accessing them using ODP.NET

Stored functions (or even simply functions) are also very similar to the stored procedures, but they do return values (directly) to the calling programs or applications.  Even though you can still use IN, OUT and IN OUT types of parameters with functions, we rarely use IN OUT.

Now let us start with a simple function definition as follows:

FUNCTION "SCOTT"."GET_DNAME" (
  "ENO" IN NUMBER) RETURN VARCHAR2 IS

    deptname dept.DNAME%type;
BEGIN -- executable part starts here
    select dname into deptname from dept
      where deptno = (select deptno from emp
        where empno = "ENO");
    return deptname;
END "GET_DNAME";

The above function simply returns a department name based on the employee number we pass to it.  I used a sub-query to get the department name in the above scenario.  Now let us look into the ODP.NET part of our ASP.NET application.  It is programmed something like this:

Dim cn As New OracleConnection("User ID=scott;password=tiger;Data
Source=ORCL")
    Try
        Dim cmd As New OracleCommand("get_dname", cn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim p_dname As New OracleParameter("deptname",
OracleDbType.Varchar2, 25)
        p_dname.Direction = ParameterDirection.ReturnValue
        cmd.Parameters.Add(p_dname)
        cmd.Parameters.Add("ENO", OracleDbType.Int32).Value =
7369
        cn.Open()
        cmd.ExecuteNonQuery()
        Me.lblMsg.Text = "Dept. name of the employee: " &
cmd.Parameters("deptname").Value.ToString
        cmd.Dispose()
    Catch ex As Exception
        Me.lblMsg.Text = ex.Message
    Finally
        If cn.State = ConnectionState.Open Then
            cn.Close()
        End If
    End Try

I hope you can easily understand the above program except for the following set of statements.

Dim p_dname As New OracleParameter("deptname",
OracleDbType.Varchar2, 25)
p_dname.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(p_dname)
cmd.Parameters.Add("ENO", OracleDbType.Int32).Value = 7369

Within the above set of statements, we declared a parameter named “deptname” which is of type “varchar2” and has a maximum size of 25.  The direction of that parameter has been specified as “ParameterDirection.ReturnValue”, which means that our application can expect a value directly from that function itself.  I hope you can understand “ENO” very easily as it is simply an IN parameter to the function.  We retrieve back the return value using the following statement:

cmd.Parameters("deptname").Value.ToString

In my upcoming article, I would focus on Packages and other stuff.  Any comments, suggestions, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.