Try this. I did not test this code, but copied bits and pieces from what I
used. Let me know if you run into any issues.
Public Sub SomeName()
Dim objCmd As New OracleCommand("sp_MyProc")
Dim myDT As DataTable
Dim myDA as OracleDataAdapter = New OracleDataAdapter
objCmd.CommandType = CommandType.StoredProcedure
With objCmd.Parameters
.Add(CreateStringParameter("StateName", strStateName))
.Add(CreateStringParameter("CityName", strCityName))
.Add(CreateNumericParameter("ZipCode", strZipCode))
.Add("P_CUR", OracleType.Cursor).Direction =
ParameterDirection.Output
End With
'Set your Connection string etc and open an Oracle DB Connection
here...
'I used the Oracle ODP.NET provider for this code
objCmd.Connection = dbConn
dbConn.Execute(objCmd) 'dbConn is my database connection object
With myDA
.SelectCommand = sCommand
.Fill(myDT)
.Dispose()
End With
'myDT should now have the results of the Cursor populated. You can
slightly change this to use a data reader.
End Sub
Private Function CreateNumericParameter(ByRef ParamName As String, _
ByVal Value As Long) As
OracleParameter
If Value = 0 Then
Return New OracleParameter(ParamName, DBNull.Value)
Else
Dim X As New OracleParameter(ParamName, OracleType.Number)
X.Value = Value
Return X
End If
End Function
Private Function CreateStringParameter(ByRef ParamName As String, _
ByVal Value As String, _
Optional ByVal Size As Integer
= -1) As OracleParameter
If Value = "" Then
Return New OracleParameter(ParamName, DBNull.Value)
Else
If Size = -1 Then
Dim X As New OracleParameter(ParamName, OracleType.VarChar)
X.Value = Value
Return X
Else
Dim X As New OracleParameter(ParamName, OracleType.VarChar,
Size)
X.Value = Value
Return X
End If
End If
End Function
Regards,
Trevor
