Oracle Database Interaction Using ODP.NET and ASP.NET: Accessing Stored Procedures, Functions - Accessing a simple PL/SQL procedure with OUT parameter using ODP.NET
(Page 2 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_GETCOUNT", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("reccount", OracleDbType.Int32).Direction
= ParameterDirection.Output
cn.Open()
cmd.ExecuteNonQuery()
Me.lblMsg.Text = cmd.Parameters("reccount").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_GETCOUNT”. The most important statements to concentrate on here are the following:
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("reccount", OracleDbType.Int32).Direction =
ParameterDirection.Output
The first statement confirms that the “OracleCommand” needs to execute a stored procedure (and not a query or DML statement). The second statement is the heart, which deals with parameter direction.
Within the second statement, I am trying to add a parameter “recount” with a “direction” as “ParameterDirection.Output”. This gets mapped properly with the output parameter specification of our PL/SQL stored procedure above. If no “ParameterDirection” is specified, it takes the “ParameterDirection.Input” as default. So, it is very much essential to specify “ParameterDirection.Output” in the above scenario.
That’s all and the rest will be automatically taken care of by ODP.NET, ASP.NET and the Oracle database! We retrieve value back from the OUT parameter using the following statement:
cmd.Parameters("reccount").Value
Next: What about IN OUT parameter in a PL/SQL procedure? >>
More ASP.NET Articles
More By Jagadish Chaterjee
