Well, we are not using C# in house, we are working in VB right now. But
this is the Sub we use for calling procedures in packages (hopefully you
shouldnt have too much trouble converting it). It makes some assumptions
about Procedure returns and such based on our business rules, so you may
need to change it a bit, but weve had no problems with it.
'// BEGIN CODE =========================================================
#Region " Helper Class "
Public Class clsParamDef
Public Name As String
Public Type As OracleType
Public Direction As ParameterDirection
Public Value As Object
Public Size As Integer
Public Sub New(ByVal Name As String, _
ByVal Type As OracleType, _
ByVal Direction As ParameterDirection, _
Optional ByVal Value As Object = Nothing, _
Optional ByVal Size As Integer = Nothing)
Me.Name = Name
Me.Type = Type
Me.Direction = Direction
Me.Value = Value
Me.Size = Size
End Sub
End Class
#End Region
Public Module DB
Public Function ExecuteProcedure _
(ByVal ProcName As String, ByRef Args() As clsParamDef) As Object
Try
'// Name of RETURN parameter if there is one
Dim returnParamName As String = ""
'// Remember if there are any OUTPUT params
Dim wantCursors As Boolean = False
Dim conConnection As OracleConnection = New OracleConnection
Dim cmdCommand As OracleCommand = New OracleCommand
cmdCommand.Connection = conConnection
Dim da As OracleDataAdapter = Nothing
Dim ds As DataSet = Nothing
'// ConnectionString form:
'// "Data Source=DEVELOP;User ID=User1;Password=PWD"
conConnection.ConnectionString = ConnectionString()
cmdCommand.CommandType = CommandType.StoredProcedure
cmdCommand.CommandText = ProcName
'// set up the parameters on the command object
cmdCommand.Parameters.Clear()
Dim cp As clsParamDef
For Each cp In Args
cmdCommand.Parameters.Add(cp.Name, cp.Type).Direction _
= cp.Direction
cmdCommand.Parameters(cp.Name).Value = cp.Value
If Not IsNothing(cp.Size) AndAlso cp.Size > 0 Then _
cmdCommand.Parameters(cp.Name).Size = cp.Size
If cp.Type = OracleType.Cursor Then wantCursors = True
If cp.Direction = ParameterDirection.ReturnValue Then _
returnParamName = cp.Name
Next
'// excute the command
conConnection.Open()
If wantCursors Then
da = New OracleDataAdapter(cmdCommand)
ds = New DataSet("results")
da.Fill(ds, "results")
Else
cmdCommand.ExecuteNonQuery()
End If
conConnection.Close()
'update any in/out parameters here
For Each cp In Args
If cp.Direction = ParameterDirection.InputOutput Or _
cp.Direction = ParameterDirection.Output Then
If wantCursors And cp.Type = OracleType.Cursor Then
cp.Value = ds
Else
cp.Value = cmdCommand.Parameters(cp.Name).Value
End If
End If
Next
mobjLastEx = Nothing
'// return the return value if there is one
If returnParamName <> "" Then _
Return cmdCommand.Parameters(returnParamName).Value
Catch e As Exception
If conConnection.State <> ConnectionState.Closed Then _
conConnection.Close()
Throw e
Finally
If conConnection.State <> ConnectionState.Closed Then _
conConnection.Close()
End Try
End Function
End Module
'// END CODE ===========================================================