Calling Oracle Stored Procedure from VB .NET

Hello,
Calling Oracle Stored Procedure from VB .NET is not something new but since a Don from Cartersville asked me this question, I will explain it here. In general here is the source code to perfome this task:

Public Sub CallOracleStoreProceudre()
Dim conn As New OracleConnection("data source=baridsoft;user id=ali, pwd=khademi")
Try
conn.Open()
Dim cmd As New OracleCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "OracleSP"
Dim p1 As OracleParameter
Dim p2 As OracleParameter

p1 = cmd.Parameters.Add("Param1", OracleType.NVarChar)
p1.Value = "Value1"
p2 = cmd.Parameters.Add("Param2", OracleType.Double)
p2.Value = 10

cmd.ExecuteNonQuery()

Catch ex As Exception
'Handle exceptions here
End Try
conn.close()
End Sub

This VB .NET subroutine calls OracleSP which accepts 2 parameters. It is quite possible to return a result set to calling subroutine. To do so replace the call to
cmd.ExecuteNonQuery with this line:

dim r as new SqlDataReader = cmd.ExecuteReader()

Then you can iterate through your data and process it. (like below)
while r.read
' Do what ever you want
end while


ADO .NET has got wonderfull capabilities when coming to RESTfull data sources. I will cover it sometimes latter.

Here is an example of calling Oracle Stored Function as well:

Public Sub CallOracleStoredFunction()
Dim conn As New OracleConnection("data source=BaridSoft;user id=Ali Khademi;pwd=DoNotAskDoNotTell")
Try
conn.Open()
Dim cmd As New OracleCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "sf_my_function"
Dim p1 As OracleParameter
Dim p2 As OracleParameter
Dim p3 As OracleParameter
Dim p4 As OracleParameter

p1 = cmd.Parameters.Add("param1", OracleType.NVarChar)
p1.Value = "123456"
p2 = cmd.Parameters.Add("param2", OracleType.NVarChar)
p2.Value = "Mn"
p3 = cmd.Parameters.Add("param3", OracleType.Number)
p3.Value = 10
p4 = cmd.Parameters.Add("v", OracleType.Int16)
p4.Direction = ParameterDirection.ReturnValue
Dim n As Integer
n = cmd.ExecuteNonQuery()
MsgBox("Function successfully executed. Return value is " + p4.Value.ToString, MsgBoxStyle.OkOnly, "Function Call")
conn.Close()
Catch ex As Exception
' handle exceptions here
End Try
End Sub



Be a winner in your daily challenges

Ali Khademi