Oracle Database Interaction Using ODP.NET and ASP.NET: Accessing Stored Procedures, Functions - Accessing the IN OUT parameter using ODP.NET
(Page 4 of 5 )
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
Next: A word about stored functions and about accessing them using ODP.NET >>
More ASP.NET Articles
More By Jagadish Chaterjee
