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.
<%@ 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>
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,