ADO .Net : Calling Oracle Function from ADO .Net

Http://www.code4coder.com    
Calling Oracle function from ADO .Net (using VB .Net) is really easy. Only trick is that you need to add "return" type parameter in command object. Although most of my blog entries are in C#, but this time I am going with VB .Net J

VB .Net code to call Oracle Function using ADO .Net:

1. Assume that function is defined in oracle package with following signature:
   1. Package_Name.Function_Name(pi_input_value IN NUMBER) RETURN VARCHAR;
      1. Name of package : Package_Name
      2. Name of Function : Function_Name
      3. Parameter : pi_input_value of NUMBER type and 'IN' direction
      4. Return type is VARCHAR.
2. Create command object:

• Dim oCommand As New OracleCommand
• With oCommand
• .Connection = New OracleConnection("Oracle Connection String")
• .CommandType = CommandType.StoredProcedure
• .CommandText = "promo.drs_ecom_ns_item.get_email_sub_prefix"
• .Parameters.Add(New OracleParameter("pi_input_value ", System.Data.OracleClient.OracleType.Number)).Value = 100
• .Parameters.Add(New OracleParameter("return_value", System.Data.OracleClient.OracleType.VarChar, 100)).Direction = ParameterDirection.ReturnValue
• End With

1. Open the connection and execute query:

• oCommand.Connection.Open()
• oCommand.ExecuteNonQuery()
• Get return value of function as following:
  • Dim retValue As String = Convert.ToString(oCommand.Parameters("return_value").Value)
    

Note : you can give any name to return value parameter

PS:

• This blog is written on topic : 'Calling Oracle function/Package from .Net' using tools/language : ADO .Net & VB .Net & Oracle.
• Keywords: Calling Oracle Function through .Net, Getting return value of oracle function in .Net, Calling Oracle package with return value using .Net / VB .Net
• VB .Net = Visual Basic .Net
• Anyone can copy this blog entry, but please refer back to http://code4coder.com