Calling an Oracle Stored Procedure with parameters

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

How do I callthe following Oracle Stored Procedure in Vb.Net 2005?

create or replace PROCEDURE sp_MyProc (StateName VARCHAR2 default null,
CityName VARCHAR2 default null,
ZipCode number default null,
p_cur out sys_refcursor)

When I use the following; I get an error "Not all variables bound":
Dim str1 As String = "Position"
objCmd.CommandText = "sp_MyProc(StateName => str1, CityName =>null,
ZipCode >null, p_cur => :c)"
 
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
 
Back
Top