Public Function VALIDATE_NUMBER(ByVal num As String)
'connection string
oraconnstring = "Data Source=" & OracleDataSource & ";User ID=" & OracleUserID & ";Password=" & OraclePassword & ";"
'initialize command
Dim oraclecmd As New OracleCommand
oraConnection.Open()
'Initiate the package command
oraclecmd = oraConnection.CreateCommand
oraclecmd.CommandType = CommandType.StoredProcedure
oraclecmd.CommandText = "CUSTOM_PACKAGE.GET_NUM"
'create parameters that will be passed with command
Dim dbNum As OracleParameter
Dim dbPartNum As OracleParameter
Dim dbCode As OracleParameter
'serialnumber is sent to package
dbNum = oraclecmd.Parameters.Add("I_STR_NUMBER", OracleDbType.Varchar2)
dbNum.Value = serialnum
dbNum.Direction = ParameterDirection.Input
dbNum.Size = 25 'value is modified by oracle package
dbPartNum = oraclecmd.Parameters.Add("O_STR_PART_NUMBER", OracleDbType.Varchar2)
dbPartNum.Value = DBNull.Value
dbPartNum.Direction = ParameterDirection.InputOutput
dbPartNum.Size = 25
'value is modified by oracle package
dbCode = oraclecmd.Parameters.Add("O_STR_HEAT_CODE", OracleDbType.Varchar2)
dbCode.Value = DBNull.Value
dbCode.Value = "default"
dbCode.Direction = ParameterDirection.InputOutput
dbCode.Size = 25
oraclecmd.ExecuteNonQuery()
'populate class
numinfo.PartNumber = dbForgingPartNum.Value.ToString
numinfo.Code = dbHeatCode.Value.ToString
Return numinfo
oraConnection.Dispose()
end function
-------------
if the package returns a boolean value, which vb.net/oracle adapter can't handle, then a solution is to create a wrapper around that package.
oraclecmd = oraConnection.CreateCommand
'initiate return value
Dim ReturnValue As OracleParameter = oraclecmd.Parameters.Add(":ReturnValue", OracleDbType.Varchar2)
ReturnValue.Direction = ParameterDirection.Output
ReturnValue.Size = 1
'Wrapper for the stored function (anonymous block)
'boolean datatype is not available outside of pl/sql
oraclecmd.CommandType = CommandType.Text
oraclecmd.CommandText = "declare tmp boolean; ReturnValue varchar2(1) ;begin tmp := FS.GETSN('" & value & "');if tmp = true then :ReturnValue := 'T'; else ReturnValue := 'F'; end if; end;"
'execute the anonymous block
oraclecmd.ExecuteNonQuery()
'convert returnvalue into a string
Dim a As String = oraclecmd.Parameters(":ReturnValue").Value.ToString
A will be T or F.
