Oracle Database Interaction Using ODP.NET and ASP.NET: Accessing PL/SQL Stored Procedures - A simple PL/SQL procedure with a single parameter
(Page 3 of 4 )
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.
Next: A simple PL/SQL procedure with multiple parameters >>
More ASP.NET Articles
More By Jagadish Chaterjee
