Oracle Database Interaction Using ODP.NET and ASP.NET: Accessing PL/SQL Stored Procedures - A simple PL/SQL procedure with multiple parameters
(Page 4 of 4 )
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 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_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)
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
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!
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
cmd.Parameters.Add(New OracleParameter("INC_AMT",
200))
cmd.Parameters.Add(New OracleParameter("DNO", 20))
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
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:
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
cmd.Parameters.Add("INC_AMT", OracleType.Number).value=200
cmd.Parameters.Add("DNO", OracleType.Number).value=20
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
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
