HOWTO : call stored procedure with result function and input/ouput parameters and Oracle database

[pbaillard]

Here a sample to call a stored procedure with an Oracle Database.

odc.Connection = m_cDb ' use an open connection to your database
odc.CommandType = CommandType.StoredProcedure
odc.CommandText = "pkg_func.GetReqStatus" ' just set the name of the
function, don't used syntax like {? = call
pkg_func.GetReqStatus(?,?,?)}

' the return parameter
odp = New OleDbParameter("result", OleDbType.VarChar)
odp.Direction = ParameterDirection.ReturnValue
odp.Size = 255 ' don't forget the size or you will got an error
odc.Parameters.Add(odp)

' the input parameter
odp = New OleDbParameter("req_id", OleDbType.Integer)
odp.Direction = ParameterDirection.Input
odp.Value = 1024
odp.Size = 10
odc.Parameters.Add(odp)

' the first output parameter
odp = New OleDbParameter("status", OleDbType.VarChar)
odp.Direction = ParameterDirection.Output
odp.Size = 255
odc.Parameters.Add(odp)

' the second output parameter
odp = New OleDbParameter("data", OleDbType.VarChar)
odp.Direction = ParameterDirection.Output
odp.Size = 1024
odc.Parameters.Add(odp)

odc.ExecuteNonQuery()

' now get return values
Dim sResult As String = CType(odc.Parameters("result").Value(),
String)
Dim sStatus As String = CType(odc.Parameters("status").Value(),
String)
Dim sData As String = CType(odc.Parameters("data").Value(), String)

I use the Oracle Client v10.2 and the 'Oracle Provider for OLE DB'
installed with the client.