Calling Oracle Stored Procedure From ASP.NET

In this article, I want to show you how to call a stored procedure of Oracle Database 10g from ASP.NET using ADO.NET. Just following the steps given below to call a stored procedure called ChangeSalary of Oracle database 10g.
  1. Connect to Oracle10g using schema HR with password HR.
  2. While you are at SQL*PLUS, crate a stored procedure called ChangeSalary as follows:
    create procedure ChangeSalary(empid number, sal number) is
    begin
        update employees set salary = sal where employee_id = empid;
    end;
    /
    
    Even / at the end is required.

  3. There is nothing special about this procedure. It takes two parameters and changes the salary of an employee. Empid represents employee id and sal contains the new salary of the employee. Now job is done with Oracle. So, let us go to Visual web developer 2005/Visual Studio.Net 2005.

  4. Create a new website using File->New Website and select Empty project option. Enter any name you like for the project.
  5. Go to Solution Explorer, select project and right click to invoke popup menu.
  6. Select Add Reference option from the popup menu
  7. From .NET tab in Add Reference dialog box, select System.Data.OracleClient library and click on Ok.
    This library is required to use .NET data provider for Oracle.
  8. Add a Web Form to the project using Website->Add New Item and select Web Form as the option from template.
  9. Create interface to take employee id and new salary - Two textboxes, a button and a label for displaying message.
  10. Write code for button to call Oracle stored procedure. The complete source code for .ASPX file is given below:
    <%@ Page Language="C#" %>
    <%@ Import Namespace="System.Data.OracleClient" %>
    <%@ Import Namespace="System.Data" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <script runat="server">
    
        protected void Button1_Click(object sender, EventArgs e)
        {
            OracleConnection con = new OracleConnection("uid=hr;pwd=hr");
            try
            {
                con.Open();
                OracleCommand spcmd = new OracleCommand("ChangeSalary");
                spcmd.CommandType = CommandType.StoredProcedure;
                spcmd.Connection = con;
                spcmd.Parameters.Add("empid", OracleType.Number, 5).Value = txtEmpid.Text;
                spcmd.Parameters.Add("sal", OracleType.Number, 5).Value = txtSal.Text;
                spcmd.ExecuteNonQuery();
    
                // now retreive data and display new salary
    
                OracleCommand qcmd = new OracleCommand("select salary from employees where employee_id = :empid", con);
                qcmd.Parameters.Add("empid", OracleType.Number, 5).Value = Int32.Parse(txtEmpid.Text);
                
                Object salary = qcmd.ExecuteScalar();
                
                lblMsg.Text = String.Format("New salary = {0}", salary.ToString());
            }
            catch (Exception ex)
            {
                lblMsg.Text = "Error --> " + ex.Message;
            }
            finally
            {
                con.Close();
            }
            
        }
    </script>
    
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
        <title>Untitled Page</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <h3>
                Calling Stored Procedure</h3>
            <table>
                <tr>
                    <td style="width: 147px">
                        Enter employee id :</td>
                    <td style="width: 38px">
                        <asp:TextBox ID="txtEmpid" runat="server"></asp:TextBox></td>
                </tr>
                <tr>
                    <td style="width: 147px">
                        Enter new salary :
                    </td>
                    <td style="width: 38px">
                        <asp:TextBox ID="txtSal" runat="server"></asp:TextBox></td>
                </tr>
            </table>
            <br />
            <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Call Procedure" /><br />
            <br />
            <asp:Label ID="lblMsg" runat="server" Font-Bold="True"></asp:Label></div>
        </form>
    </body>
    </html>
    
  11. The following are important points related to above code:
  12. Run the above page and enter a valid employee id and new salary as 4000 then you must see a message saying New salary = 4000.

Though overall process is same as what we do for Sql Server, there are some important differences especially in the way you refer to a parameter. Remember in SQL Server, you given @name for paramter whereas the same is done using :name in Oracle.

Keep Learning,
P.Srikanth.